CODEWITHSUNDEEP
sqlsql-servert-sqlmaxaggregate-functions
user1422348
user1422348

Reputation: 355

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?

Upvotes: 1

Views: 120

Answers (3)

The Hill Boy
The Hill Boy

Reputation: 162

Try this.

DECLARE @TAB2 TABLE(LASTNAME VARCHAR(40), FIRSTNAME VARCHAR(40), FILENAME VARCHAR(40)) 
INSERT INTO @TAB2 VALUES 
( 'Smith',        'John',          '001'),
( 'Smith',        'John',          '002'),
( 'Smith',        'Anna',          '003'),
( 'Smith',        'Anna',          '004')

SELECT 
    LASTNAME, 
    FIRSTNAME, 
    FILENAME,
    CASE ROW_NO WHEN 2 THEN FILENAME ELSE NULL END AS  MAX_FILENAME
FROM 
(
    SELECT 
        LASTNAME, 
        FIRSTNAME, 
        FILENAME,
        ROW_NO = ROW_NUMBER() OVER (PARTITION BY FIRSTNAME ORDER BY FILENAME ASC) 
     FROM @TAB2
 )A

Upvotes: 0

roman
roman

Reputation: 117350

actually you're very close, but instead of using max as simple aggregate you can use max as window function:

select
    Lastname, Firstname, Filename,
    case
        when Filename = max(Filename) over(partition by Lastname, Firstname) then Filename
        else null
    end as Max_Filename
from Table1

sql fiddle demo

Upvotes: 5

Szymon
Szymon

Reputation: 43023

It could be something like that:

SELECT 
    T.Lastname, 
    T.FirstName, 
    T.Filename,
    CASE (SELECT MAX(T1.Filename) FROM MyTable T1 
            WHERE T.Lastname = T1.Lastname AND T.FirstName = T1.FirstName)
        WHEN T.Filename THEN T.Filename
        ELSE NULL
    END
FROM MyTable T

But I'm not sure what you mean by max filename? Total max from all records? Or separately for each name? Your expected result don't match either. Let me know and I'll modify the query.

Upvotes: 1

Related Questions