CODEWITHSUNDEEP
postgresql
eraelpeha
eraelpeha

Reputation: 419

Join column with timestamps where value is maximum

I have a table that looks like

+-------+-----------+
| value | timestamp |
+-------+-----------+

and I'm trying to build a query that gives a result like

+-------+-----------+------------+------------------------+
| value | timestamp | MAX(value) | timestamp of max value |
+-------+-----------+------------+------------------------+

so that the result looks like

+---+----------+---+----------+
| 1 | 1.2.1001 | 3 | 1.1.1000 |
| 2 | 5.5.1021 | 3 | 1.1.1000 |
| 3 | 1.1.1000 | 3 | 1.1.1000 |
+---+----------+---+----------+

but I got stuck on joining the column with the corresponding timestamps.

Any hints or suggestions? Thanks in advance!

For further information (if that helps): In the real project the max-values are grouped by month and day (with group by clause, which works btw), but somehow I got stuck on joining the timestamps for max-values.

EDIT

Cross joins are a good idea, but I want to have them grouped by month e.g.:

+---+----------+---+----------+
| 1 | 1.1.1101 | 6 | 1.1.1300 |
| 2 | 2.6.1021 | 5 | 5.6.1000 |
| 3 | 1.1.1200 | 6 | 1.1.1300 |
| 4 | 1.1.1040 | 6 | 1.1.1300 |
| 5 | 5.6.1000 | 5 | 5.6.1000 |
| 6 | 1.1.1300 | 6 | 1.1.1300 |
+---+----------+---+----------+

EDIT 2

I've added a fiddle for some sample data and and example of the current query. http://sqlfiddle.com/#!1/efa42/1

How to add the corresponding timestamp to the maximum?

Upvotes: 2

Views: 386

Answers (2)

devanand
devanand

Reputation: 5290

Use the window cause you use with pg

 Select *, max( value ) over (), max( timestamp ) over() from table

That gives you the max values from all values in every row

http://www.postgresql.org/docs/9.1/static/tutorial-window.html

Upvotes: -1

Houari
Houari

Reputation: 5621

Try a cross join with two sub queries, the first one selects all records, the second one gets one row that represents the time_stamp of the max value, <3;"1000-01-01"> for example.

SELECT col_value,col_timestamp,max_col_value, col_timestamp_of_max_value FROM table1
    cross join
      (
        select max(col_value) max_col_value ,col_timestamp col_timestamp_of_max_value  from table1
        group by col_timestamp
        order by max_col_value desc
        limit 1
      ) A --One row that represents the time_stamp of the max value, ie: <3;"1000-01-01">

Upvotes: 2

Related Questions