Retrieve only the first row from each duplicate id in MySQL-CodePudding

I want to retrieve only the first row from each duplicate id in a table. The issue is that every other field except the id is unique, so that means I cannot "filter" the rows.

Here is my scientist table:

id	firstname	lastname
1	albert	einstein
1	albert1	einstein1
1	albert2	einstein2
1	albert3	einstein3
2	isaac	newton
2	isaac1	newton1
3	marie	curie
3	marie1	curie1
3	marie2	curie2

Note: The firstname and the lastname are irrelevant I just want to extract the first row of each id.

And below you can find my desired output:

id	firstname	lastname
1	albert	einstein
2	isaac	newton
3	marie	curie

I have tried to GROUP BY id but it is not working.

Also, I have tried to select the scientist table based on the distinct ids of the same table

SELECT * FROM scientist WHERE id IN (SELECT DISTINCT id FROM scientist)

But then I realised that my logic was incorrect.

CodePudding user response：

Try it here enter link description here

 select id,firtname,lastname
        from(    
        select *, ROW_NUMBER() OVER(PARTITION BY scientist.id) c
            from scientist) t
        Where c = 1