-1

I've tried a few solutions but can't quite get this to work.

Here's my table:

id,name,title,year
1,Bill,Apple Tree,2010
2,Bill,Cheese Factory,1998
3,Bill,Pie Shop,2005
4,Ted,Vegetable Path,2004
5,Ted,Fruit Farm,1984
6,Ted,Chocolate Factory,2010
7,Death,Pumpkin Patch,2017
8,Death,Nut Plant,2016
9,Death,Pasta Bar,2020

I would like to get the earliest year for each title for each name, so I'm expecting these results:

Bill,Cheese Factory,1998
Ted,Fruit Farm,1984
Death,Nut Plant,2016

But this is not getting me the results I need:

SELECT t1.name, t1.title, MIN(t1.year)
FROM table1 t1
GROUP BY t1.name

Thanks

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
huey
  • 115
  • 9
  • What do you mean by the earliest year? Do you mean that you want to sort the data asending depends on year? – Ahmed Nov 20 '20 at 08:13
  • 1
    Which MySQL version? – jarlh Nov 20 '20 at 08:13
  • You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. I.e. try `GROUP BY t1.name, t1.title` and see what happens. – jarlh Nov 20 '20 at 08:15

1 Answers1

1

you can use row_number()

select name,title,year from
(
select *,row_number() over (partition by name order by year) as rn
 from tablename
)A where rn=1
jarlh
  • 42,561
  • 8
  • 45
  • 63
Fahmi
  • 37,315
  • 5
  • 22
  • 31