I have table like this
| id | name |
|---|---|
| 1 | Apple |
| 1 | Banana |
| 1 | Guava |
| 2 | Cassava |
| 2 | Carrot |
| 2 | Potato |
| 3 | Almond |
| 3 | Soybeans |
| 3 | Peanuts |
I want to select only the first one from each id
| id | name |
|---|---|
| 1 | Apple |
| 2 | Cassava |
| 3 | Almond |
What's the query like?
CodePudding user response：
you can try this way
SELECT id, name FROM table_name GROUP BY id ORDER BY id ASC;
CodePudding user response：
You can achieve your goal by using row_number(). You can check my query in db-fiddle: https://www.db-fiddle.com/f/g4MrUTTTGDFfqjAKYnkFxn/1
WITH CTE as
(
SELECT id, name, ROW_NUMBER() OVER (ORDER BY null) as rowNumber FROM Fruits
)
SELECT id, name FROM CTE WHERE rowNumber IN(SELECT min(rowNumber) FROM CTE Group by id)