Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: selecting max of day for a month

    Hi
    I have table where hourly value of temp' for 12 moths of the year is stored with date,time(hour) and temp being the three columns.How can i select the time of occurance of maximum temp for each day of the year?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show the table definition (e.g. the output of \d in psql), some sample data and the expected output

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Question doesn't seem to match subject. But some permutation of the following should get you what you want.

    Code:
    SELECT date_col, hour, temperature
    FROM foo 
    JOIN (
      SELECT date_col, MAX(temperature) mt
      FROM foo
      GROUP BY date_col
    ) sub ON foo.date_col = sub.date_col AND foo.temperature  = sub.mt

  4. #4
    Join Date
    Dec 2009
    Posts
    2

    tabel definition & data

    Quote Originally Posted by shammat View Post
    Please show the table definition (e.g. the output of \d in psql), some sample data and the expected output


    Date| "Time" |"temp"
    1968-01-01|"00:00:00"|7.5
    1968-01-01|"01:00:00"|8.2
    1968-01-01|"03:00:00"|11.9
    1968-01-01|"04:00:00"|10.3
    1968-01-01|"06:00:00"|11.6
    1968-01-01|"09:00:00"|10.5
    1968-01-01|"10:00:00"|10.2
    1968-01-01|"11:00:00"|9.6
    1968-01-01|"12:00:00"|9.4
    1968-01-02|"00:00:00"|8.8
    1968-01-02|"01:00:00"|11.7
    1968-01-02|"02:00:00"|11.7
    1968-01-02|"03:00:00"|10.7
    1968-01-03|"00:00:00"|8.8
    1968-01-03|"01:00:00"|11.7
    1968-01-03|"02:00:00"|11.7
    1968-01-03|"03:00:00"|10.7

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Wow, how cool would it be if you table was named "table." Then it would be made up entirely of reserved words.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •