group by help?

pebkac · #1 (**permalink**) 10-03-05, 21:36

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!!

r937 · #2 (**permalink**) 10-03-05, 22:04

by "last" occurrence you mean the one with the largest id?

Code:

select id
     , user
     , fname
     , lname 
  from logins as ZZ
 where id 
     = ( select max(id)
           from logins
          where user = ZZ.user )

pebkac · #3 (**permalink**) 10-20-05, 19:45

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?

r937 · #4 (**permalink**) 10-20-05, 19:53

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?

Peter.Vanroose · #5 (**permalink**) 10-23-05, 14:54

If a correlated subquery is not supported, let's hope a join (and a group by) is?
Could you try this one:

Code:

  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

Internet Services

Web Development

Digital Marketing

Consumer Tech