group by help?

I am trying to get the last occurance of a display name in a login user log database. Basically I have a table that looks like this:

id user fname lname
-----------
1 jdoe Jane Doe
2 jdoe John Doe
3 jdoe Fred Flinstone

I run the MySQL query: SELECT name, max(id) as max_id, user FROM `logins` GROUP BY user

I get back:
id user fname lname
-----------
3 jdoe Jane Doe

I actually want:
id user fname lname
-----------
3 jdoe Fred Flinstone

Any that can help it would be greatly Appreciated!!by "last" occurrence you mean the one with the largest id?
select id
, user
, fname
, lname
from logins as ZZ
where id
= ( select max(id)
from logins
where user = ZZ.user )|||I tried this result and am still having difficulties? Do you know if this works with all versions of MySQL? I get the following error from phpmyadmin:

You have an error in your SQL syntax near 'select max(id) from logins where user=ZZ.user

Any other thoughts?|||good guess -- subqueries are not supported prior to version 4.1

how come it took you two and a half weeks to try my solution?|||If a correlated subquery is not supported, let's hope a join (and a group by) is?
Could you try this one: select a.id, a.user, a.fname, a.lname
from logins as a, logins as b
where a.user = b.user
and a.id <= b.id
group by a.id, a.user, a.fname, a.lname
having count(*) = 1

Group By

Hi,

I have the report,Report datasource is stored procedure.The stored procedure is returning results like this:

ID Name Count

1 Own Residence 50

2 Other Residence 20

3 xxxx 89

4 vvvvvv 78

Now i want to display the report like in the following format:

Name Count

Residence

Own 50

Other 20

xxxx 89

vvvvvv 78

How to achevie this

Thanks in advance

Assuming you can't change your store procedure.

To do this you need two distinct tables on your report designer surface. One will have a filter to get rid of all names without 'Residence'. Then, you simply list the names and counts in the details section.

Right underneth, you need to add a second table without heading/footer. This one will have a filter to get rid of the name with 'Residence'. Make the column widths match the one of your first table and things should align properly as if you were using a single table.

Good luck.