CODEWITHSUNDEEP
postgresql
Agenobarb
Agenobarb

Reputation: 153

Timestamp of a max and min values

I have a following table:

   id    | product_id | price |         timestamp          
---------+------------+-------+----------------------------
 1263479 |          1 |  1100 | 2019-01-21 21:08:30.617791
  272859 |          1 |  1100 | 2018-12-01 11:31:40.746908
  139210 |          2 |  1100 | 2018-11-13 14:24:19.775732
  193796 |          2 |   880 | 2018-11-23 13:17:20.820132

How I can select min(price) and its timestamp, max(price) and its timestamp in one query please?

Upvotes: 0

Views: 255

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522499

You may use ROW_NUMBER twice:

WITH cte AS (
    SELECT id, product_id, price, timestamp,
        ROW_NUMBER() OVER (ORDER BY price) rn_min,
        ROW_NUMBER() OVER (ORDER BY price DESC) rn_max
    FROM yourTable
)

SELECT 'min' AS label, price, timestamp FROM cte WHERE rn_min = 1
UNION ALL
SELECT 'max', price, timestamp FROM cte WHERE rn_max = 1;

If you don't want the min and max labels (the prices themselves giving away which one is the minimum and which one the maximum), then you may just use this:

SELECT price, timestamp FROM cte WHERE rn_min = 1 OR rn_max = 1;

Upvotes: 1

Related Questions