Mysql Max(id)???

[tom_b]

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?

 

[artacus]

SELECT MAX(id) FROM myTable

 

Or if you want the whole row

SELECT *

FROM myTable

WHERE id = (SELECT MAX(id) FROM myTable)

[utexas_pjm]

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

[tom_b]

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

[btherl]

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.

[tom_b]

O.K. that last one is good, getting the data I need, is there a way to sort them by the numbers?

[btherl]

Just add "ORDER BY numbers" or "ORDER BY numbers DESC" to the very end of the query.

[tom_b]

Hi, well I've already tried both of those, keep getting the same results, still ordered by id

[btherl]

Did you add it to the subquery (inside the brackets) or to the outer query (after the final closing bracket) ?

[tom_b]

That did it!!  I had it inside, putting it outside gave me exactly what I needed, thank you!!!!!!!

 

Tom