CODEWITHSUNDEEP
sqlpostgresql
user52219
user52219

Reputation: 25

group by on one column, but select multiple

I'm using PostgreSQL and I would like to ask how is it possible to transform a table like that:

|id |value|category|
|---|-----|--------|
| 1 |  10 |    A   |
| 2 |  90 |    A   |
| 3 |  80 |    B   |
| 4 |  20 |    B   |

in a table like that:

|id |value|category|
|---|-----|--------|
| 1 |  10 |    A   |
| 4 |  20 |    B   |

For each category I keep a minimum value of a category and corresponding to this value id. I've tried a request like this, but of course it doesn't work:

SELECT id, MIN(value), category 
FROM table 
GROUP BY category

I would like to ask what will be the correct request.
I've already search in internet, but didn't found a proper response.
Thanks in advance.

Upvotes: 1

Views: 405

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In Postgres use distinct on:

select distinct on (category) t.*
from t
order by category, value;

Upvotes: 1

Related Questions