0

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?

U13-Forward
  • 69,221
  • 14
  • 89
  • 114
Azka
  • 9
  • 2
  • Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – U13-Forward Sep 29 '21 at 04:04
  • `I want to select only the first one` ... there needs to be a third column which records which one is the "first." Your question, in its current form, is not answerable. – Tim Biegeleisen Sep 29 '21 at 04:05

2 Answers2

1

you can try this way

SELECT id, name FROM table_name GROUP BY id ORDER BY id ASC;
Masud Morshed
  • 87
  • 1
  • 10
0

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)
Md. Tarikul Islam Soikot
  • 455
  • 1
  • 4
  • 14