SQL - SELECT MAX() and accompanying field

database, join, sql

Solution

If you're on SQL Server 2005 or higher,

SELECT  UserID, UserName, EmailAddress, Source
FROM    (SELECT UserID, UserName, EmailAddress, Source,
                ROW_NUMBER() OVER (PARTITION BY UserID
                                   ORDER BY EmailAddress DESC) 
                    AS RowNumber
         FROM   MyTable) AS a
WHERE   a.RowNumber = 1

Of course there are ways to do the same task without the (SQL-Standard) ranking functions such as `ROW_NUMBER`, which SQL Server implemented only since 2005 -- including nested dependent queries and self left joins with an `ON` including a '>' and a `WHERE ... IS NULL` trick -- but the ranking functions make for code that's readable and (in theory) well optimizable by the SQL Server Engine.

Edit: this article is a nice tutorial on ranking, but it uses `RANK` in the examples instead of `ROW_NUMBER` (or the other ranking function, `DENSE_RANK`) -- the distinction matters when there are "ties" among grouped rows in the same partition according to the ordering criteria. this post does a good job explaining the difference.

Problem

What I have is basically a problem which is easily solved with multiple tables, but I have only a single table to do it. Consider the following database table ``` UserID UserName EmailAddress Source 3K3S9 Ben ben@myisp.com user SF13F Harry lharry_x@hotbail.com 3rd_party SF13F Harry reside@domain.com user 76DSA Lisa cake@insider.com user OL39F Nick stick@whatever.com 3rd_party 8F66S Stan myman@lol.com user ``` I need to select all fields, but only who each user once along with one of their email addresses (the "biggest" one as determined by the MAX() function). This is the result I am after ... ``` UserID UserName EmailAddress Source 3K3S9 Ben ben@myisp.com user SF13F Harry lharry_x@hotbail.com 3rd_party 76DSA Lisa cake@insider.com user OL39F Nick stick@whatever.com 3rd_party 8F66S Stan myman@lol.com user ``` As you can see, "Harry" is only shown once with his "highest" email address the correcponding "source" Currently what is happening is that we are grouping on the UserID, UserName, and using MAX() for the EmailAddress and Source, but the max of those two fields dont always match up, they need to be from the same record. I have tried another process by joining the table with itself, but I have only managed to get the correct email address but not the corresponding "source" for that address. Any help would be appreciated as I have spent way too long trying to solve this already :)

Original source