Sunday, 29 September 2013

SQL Server Join based on max value

SQL Server Join based on max value

I was wondering how I can left join a table to itself or use a case
statement to assign max values within a view. Say I have the following
table:
Lastname Firstname Filename
Smith John 001
Smith John 002
Smith Anna 003
Smith Anna 004
I want to create a view that lists all the values but also has another
column that displays whether the current row is the max row, such as:
Lastname Firstname Filename Max_Filename
Smith John 001 NULL
Smith John 002 002
Smith Anna 003 NULL
Smith Anna 004 NULL
Is this possible? I have tried the following query:
SELECT Lastname, Firstname, Filename, CASE WHEN Filename = MAX(FileName)
THEN Filename ELSE NULL END AS Max_Filename
but I am told that Lastname is not in the group by clause. However, if I
group on Lastname, firstname, filename, then everything in the
max_filename is the same as filename.
Can you please help me understand what I'm doing wrong and how to make
this query work?
Thanks in advance!

No comments:

Post a Comment