Selecting the group-wise maximum

[Hi I Am Timbo]

I'm trying to write a query on a user table.  The table looks like this:

 

+--------+-----------+-----------+----------+----------------+
| idUser | username  | firstname | lastname | originalIdUser |
+--------+-----------+-----------+----------+----------------+
|      1 | tbarsness | Tim       | Barsness |              1 |
|      2 | asdf      | Tim       | Barsness |              1 |
|      3 | tbarsness | Tim       | Barsness |              1 |
|      4 | rocks     | Tim       | Barsness |              1 |
|      5 | johndoe   | John      | Doe      |              5 |
|      6 | agoobwa   | John      | agoobwa  |              5 |
|      7 | agoobwa   | John      | agoobwa  |              5 |
+--------+-----------+-----------+----------+----------------+

 

I am trying to get the whole row for group of originalIdUser having a max(idUser)

 

The query I thought should work:

 

SELECT * , max( idUser )
FROM `user`
GROUP BY originalIdUser
HAVING idUser = max( idUser ) 

 

But it returns an empty result set.  This is because this query:

 

SELECT * , max( idUser )
FROM `user`
GROUP BY originalIdUser

 

returns the first record in each group as below:

 

+--------+-----------+-----------+----------+----------------+
| idUser | username  | firstname | lastname | originalIdUser |
+--------+-----------+-----------+----------+----------------+
|      1 | tbarsness | Tim       | Barsness |              1 |
|      5 | johndoe   | John      | Doe      |              5 |
+--------+-----------+-----------+----------+----------------+

 

This is what I wanted:

 

+--------+-----------+-----------+----------+----------------+
| idUser | username  | firstname | lastname | originalIdUser |
+--------+-----------+-----------+----------+----------------+
|      4 | rocks     | Tim       | Barsness |              1 |
|      7 | agoobwa   | John      | agoobwa  |              5 |
+--------+-----------+-----------+----------+----------------+

 

I am thinking I have to join the user table to itself, but I am not sure how.  Any help would be greatly appreciated.

 

 

[artacus]

Select your MAX(idUser) in a subquery

[Hi I Am Timbo]

He's right, this worked like a charm:

 

SELECT *
FROM `user` A
WHERE idUser = (
SELECT MAX( idUser )
FROM user B
WHERE A.originalIdUser = B.originalIdUser ) 

 

Thank you artacus

[btherl]

Thankyou Timbo for a perfectly worded question   That's how artacus was able to answer it so easily.

 

It's a relief after reading so many "Please help, my file doesn't send the file into the database" type questions..

[artacus]

That's how artacus was able to answer it so easily.

 

LOL. That and the summer internship I did with the psychic friends network REALLY comes in handy. Lucky for us, fiber optic cable channels psychic energy even better than copper. Wonder how old Dion is doing, haven't seen her in  a while.

[fenway]

Thankyou Timbo for a perfectly worded question   That's how artacus was able to answer it so easily.

 

It's a relief after reading so many "Please help, my file doesn't send the file into the database" type questions..

But isn't that a correlated subquery (I'd have to run explain to check for sure)?  You should be able to use a derived table and get better performance, IMHO.

[artacus]

But isn't that a correlated subquery (I'd have to run explain to check for sure)?

It is.

What Fenway is saying is that since you are looking up all your users anyway, do the subquery in the FROM section so you can do two queries instead of n+1 queries in the WHERE clause.

[fenway]

But isn't that a correlated subquery (I'd have to run explain to check for sure)?

It is.

What Fenway is saying is that since you are looking up all your users anyway, do the subquery in the FROM section so you can do two queries instead of n+1 queries in the WHERE clause.

It's worse than n+1... it's one extra query _for every row_ in the outer query!

[artacus]

Yeah, thats 1 query for the outer and n for the inner

[fenway]

Oh, I get it... either way, it's not ideal. ;-)

[artacus]

You're right.

 

But seeing as how Timbo actually figured it out on his own instead of asking "So what's a subquery? Can you write it for me?"

[red]I[/red] wasn't going to crush his spirit.

[fenway]

Leave the crushing to me... lol.