SQL - SELECT MAX () and companion field

Question

I have a problem that can be easily solved with the help of several tables, but for this I have only one table.

Consider the following database table

UserID UserName EmailAddress Source 3K3S9 Ben [e-mail] user SF13F Harry [e-mail] 3rd_party SF13F Harry [e-mail] user 76DSA Lisa [e-mail] user OL39F Nick [e-mail] 3rd_party 8F66S Stan [e-mail] user 

I need to select all the fields, but only each user once has one of their email addresses (the "largest" defined by the MAX () function). This is the result I am after ...

 UserID UserName EmailAddress Source 3K3S9 Ben [e-mail] user SF13F Harry [e-mail] 3rd_party 76DSA Lisa [e-mail] user OL39F Nick [e-mail] 3rd_party 8F66S Stan [e-mail] user 

As you can see, "Harry" is displayed only once with his "highest" email address corresponding to the "source"

Currently, we are grouping by UserID, UserName and using MAX () for EmailAddress and Source, but the maximum of these two fields does not always coincide, they must be from the same record.

I tried a different process, attaching the table to myself, but I managed to get the correct email address, but not the appropriate "source" for this address.

Any help would be appreciated as I tried to solve this problem for too long :)

Answer 1

If you are using 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 accomplish the same task without ranking functions (SQL-Standard), such as ROW_NUMBER , which SQL Server has only implemented since 2005, including nested dependent queries and its own left connections with ON including the '>' and WHERE ... IS NULL , but the ranking functions make code that can be read and (theoretically) optimized well using SQL Server Engine.

Edit: this article is a good ranking tutorial, but it uses RANK in the examples instead of ROW_NUMBER (or another DENSE_RANK ranking DENSE_RANK ) - the difference matters when there are “links” between grouped rows in the same section according to the ordering criteria. this post does a great job explaining the difference.

Answer 2

 select distinct * from table t1 where EmailAddress = (select max(EmailAddress) from table t2 where t1.userId = t2.userId) 

Answer 3

 select distinct * from SomeTable a inner join ( select max(emailAddress), userId from SomeTable group by userId ) b on a.emailAddress = b.emailAddress and a.userId = b.userId 

Answer 4

I think I have a solution different from the one proposed:

  select *
 from foo
 where id = (
   select id
   from foo F
   where F.bar = foo.bar
   order by F.baz
   limit 1
 )

This gives you all the foo entries that have the largest bases compared to other foo files with the same bar.