CODEWITHSUNDEEP
sqlpostgresqlminamazon-redshift
Checco Dougherty
Checco Dougherty

Reputation: 61

In PostgreSQL how can I return the entire row that corresponds with the min of a value?

So if I have a table like this

id | value | detail
-------------------
12 | 20    | orange
12 | 30    | orange
13 | 16    | purple
14 | 50    | red
12 | 60    | blue

How can I get it to return this?

12 | 20 | orange
13 | 16 | purple
14 | 50 | red

If I group by id and detail it returns both 12 | 20 | orange and 12 | 60 | blue

Upvotes: 2

Views: 918

Answers (4)

MT0
MT0

Reputation: 167981

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

CREATE TABLE TEST( id INT, value INT, detail VARCHAR );
INSERT INTO TEST VALUES ( 12, 20, 'orange' );
INSERT INTO TEST VALUES ( 12, 30, 'orange' );
INSERT INTO TEST VALUES ( 13, 16, 'purple' );
INSERT INTO TEST VALUES ( 14, 50, 'red' );
INSERT INTO TEST VALUES ( 12, 60, 'blue' );

Query 1:

Not sure if Redshift supports this syntax:

SELECT DISTINCT
       FIRST_VALUE( id ) OVER wnd AS id,
       FIRST_VALUE( value ) OVER wnd AS value,
       FIRST_VALUE( detail ) OVER wnd AS detail
FROM   TEST
WINDOW wnd AS ( PARTITION BY id ORDER BY value )

Results:

| id | value | detail |
|----|-------|--------|
| 12 |    20 | orange |
| 14 |    50 |    red |
| 13 |    16 | purple |

Query 2:

SELECT t.ID,
       t.VALUE,
       t.DETAIL
FROM (
  SELECT *,
         ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY VALUE ) AS RN
  FROM   TEST
) t
WHERE  t.RN = 1

Results:

| id | value | detail |
|----|-------|--------|
| 12 |    20 | orange |
| 13 |    16 | purple |
| 14 |    50 |    red |

Upvotes: 3

dnoeth
dnoeth

Reputation: 60472

This is an easy task for a Windowed Aggregate Function, ROW_NUMBER:

select *
from
 (
   select t.*,
      row_number() 
      over (partition by id        -- for each id
            order by value) as rn  -- row with the minimum value
   from t
 ) as dt
where rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use order by and limit:

select t.*
from table t
order by value
limit 1;

If you have an index on value, an alternative method that returns all matching rows is:

select t.*
from table t
where value = (select min(value) from table t);

Then add a limit if you only want one row.

Upvotes: 0

Nick Barnes
Nick Barnes

Reputation: 21346

Postgres has a DISTINCT ON clause to address this case. With DISTINCT ON (id), a query will return only the first record for each value of id. You control which record is selected via the ORDER BY clause. In your case:

SELECT DISTINCT ON (id) *
FROM t
ORDER BY id, value

Upvotes: 1

Related Questions