CODEWITHSUNDEEP
sqlsql-servermax
Mmbauer
Mmbauer

Reputation: 59

Select records using max values for two columns

I have a table laid out similar to this. I need to select distinct vendor number that has the highest year value and the highest month value

VENDORMONTHLY:

id  Vendor    Year     month    More stuff(More columns)
---|---------|-------|-------|---------|
1  | 93000   | 2017  | 3     | sadf    |
2  | 93000   | 2017  | 2     | asdf    |
5  | 93000   | 2017  | 1     | asdf    |
3  | 93000   | 2016  | 12    | fff     |
4  | 93000   | 2016  | 11    | ffff    |
6  | 40000   | 2017  | 2     | fff     |
7  | 40000   | 2017  | 1     | fff     |
8  | 40000   | 2016  | 12    | fff     |

The result would look like this. I can not for the life of me come up with a query that will give me what I need. 

id  Vendor    Year     month    More stuff(More columns)
---|---------|-------|-------|---------|
1  | 93000   | 2017  | 3     | sadf    |
6  | 40000   | 2017  | 2     | fff     |

Any help would be greatly appreciated!

Upvotes: 0

Views: 6305

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

If you are using some database (SQL Server, Oracle, Postgres etc) that support window functions, you can rank ( or row_number if you need only one row per year-month combination per vendor)

select *
from (
    select v.*,
        rank() over (
            partition by vendor order by year desc,
                month desc
            ) rn
    from vendormonthly v
    ) v
where rn = 1;

In SQL server, same can be done in a better way using top with ties:

Select top 1 with ties *
From vendormonthly
Order by rank() over (
     partition by vendor
     order by year desc, month desc
)

Upvotes: 2

jarlh
jarlh

Reputation: 44795

Quick answer, use NOT EXISTS to verify the same id has no other row with a later year or same year but later month:

select v1.*
from VENDORMONTHLY v1
where not exists (select 1 from VENDORMONTHLY v2
                  where v2.Vendor = v1.Vendor
                    and (v2.Year > v1.year
                      or (v2.Year = v1.Year and v2.Month > v1.Month)))

Will return both rows in case of a latest row tie.

Core ANSI SQL-99. Will run on any dbms!

Upvotes: 2

Related Questions