CODEWITHSUNDEEP
sqlteradata
Justin B
Justin B

Reputation: 29

SQL querying a max value based off other columns

I'm wanting the max rank within each reservation as well as franchise over time. Any thoughts on how to query this? 

id  reservation date    franch  rank
1   1           6/1/2017    1   234
2   1           6/1/2017    1   465
3   1           6/1/2017    1   851
4   1           6/1/2017    1   956
1   1           6/2/2017    2   658
2   1           6/2/2017    2   578
3   1           6/2/2017    2   578
4   1           6/2/2017    2   954
5   2           6/1/2017    1   436
6   2           6/1/2017    1   645
7   2           6/1/2017    1   854
8   2           6/1/2017    1   145
5   2           6/2/2017    3   98
6   2           6/2/2017    3   345
7   2           6/2/2017    3   867
8   2           6/2/2017    3   909

The output should like like... but note I'm also hoping to not return multiple records such as the 578 for reservation 1 on 6/2/17 and franchise 2.

id  reservation date    franch  rank
4   1           6/1/2017    1   956
2   1           6/2/2017    2   578
3   1           6/2/2017    2   578
7   2           6/1/2017    1   854
8   2           6/2/2017    3   909

Upvotes: 1

Views: 2496

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

In most databases, I would probably go for:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.reservation = t.reservation and t2.date = t.date 
              order by t2.rank desc
              fetch first 1 row only
             );

But I don't think Teradata supports such constructs. If I assume you want the highest id, then:

select t.*
from t
where t.id = (select max(t2.id)
              from t t2
              where t2.reservation = t.reservation and t2.date = t.date 
             );

Or you can use qualify:

select t.*
from t
qualify row_number() over (partition by reservation, date order by rank desc) = 1;

The use of row_number() ensures one row per reservation/date, so you don't get duplicates.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use QUALIFY:

SELECT *
FROM tab
QUALIFY RANK() OVER(PARTITION BY reservation,date,franch ORDER BY rank DESC) = 1

Upvotes: 2

Related Questions