tom_b Posted February 28, 2007 Share Posted February 28, 2007 I have a DB set up something like this: id name numbers 1 me 10 2 you 15 3 them 20 4 me 25 5 you 30 6 them 35 what I need is to get the the last entry for each name. I've tried all sort of variations of Max(id) as well as different group by, order by etc, no luck, any ideas? Quote Link to comment Share on other sites More sharing options...
artacus Posted February 28, 2007 Share Posted February 28, 2007 SELECT MAX(id) FROM myTable Or if you want the whole row SELECT * FROM myTable WHERE id = (SELECT MAX(id) FROM myTable) Quote Link to comment Share on other sites More sharing options...
utexas_pjm Posted February 28, 2007 Share Posted February 28, 2007 I think he wants the last entry for each `name` in which case he'll want a query like... SELECT * FROM `table` GROUP BY `name` HAVING MAX(id) Best, Patrick Quote Link to comment Share on other sites More sharing options...
tom_b Posted February 28, 2007 Author Share Posted February 28, 2007 Hi! Well, thanks for your time, but no luck. The first query just gives me the last entry in the table, and the second query returns an empty set, which seems a little odd to me, but I've checked it 3 times!! Tom Quote Link to comment Share on other sites More sharing options...
btherl Posted February 28, 2007 Share Posted February 28, 2007 How about SELECT id, name, numbers FROM table WHERE id IN ( SELECT max(id) FROM numbers GROUP BY name ); HAVING won't work because the condition is based on both the actual id and max id. HAVING can only deal with aggregated data, not individual row data. Quote Link to comment Share on other sites More sharing options...
tom_b Posted February 28, 2007 Author Share Posted February 28, 2007 O.K. that last one is good, getting the data I need, is there a way to sort them by the numbers? Quote Link to comment Share on other sites More sharing options...
btherl Posted February 28, 2007 Share Posted February 28, 2007 Just add "ORDER BY numbers" or "ORDER BY numbers DESC" to the very end of the query. Quote Link to comment Share on other sites More sharing options...
tom_b Posted February 28, 2007 Author Share Posted February 28, 2007 Hi, well I've already tried both of those, keep getting the same results, still ordered by id Quote Link to comment Share on other sites More sharing options...
btherl Posted February 28, 2007 Share Posted February 28, 2007 Did you add it to the subquery (inside the brackets) or to the outer query (after the final closing bracket) ? Quote Link to comment Share on other sites More sharing options...
tom_b Posted February 28, 2007 Author Share Posted February 28, 2007 That did it!! I had it inside, putting it outside gave me exactly what I needed, thank you!!!!!!! Tom Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.