Get result with maximum values in multiple columns at once in SQL Server

Question

Lets say we have a DATES table with 3 columns: colYear(YEAR), colMonth(MONTH), colDay(DAY).

Rows are inserted like:

colYear colMonth colDay
   1       2        3   
   2       3        0   
   4       5        5   
   4       8        0    
   1      10       28

I need to find maximum date based on 3 columns. So that in this example right answer will be: 4 years, 8 months and 0 days is maximum (we check years, max are 4s, after that we check months max is 8, so we've found our result).

Is it possible? How to achieve this with sql?

Kshitij · Accepted Answer · 2012-06-14 12:18:36Z

5

use order by -

    SELECT * from DATES 
    ORDER BY colYear DESC, colMonth DESC, colDay DESC

Based on your DB you can limit the row count to 1.

so for oracle use rownum = 1 whereas for mysql it will be LIMIT 1

answered Jun 14, 2012 at 12:18

Kshitij

8,6642 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

0x49D1 Over a year ago

Works just as i wanted, thank you for nice and simple solution.

Kshitij Over a year ago

i am glad it helped... also make note of what Sparky mentioned above if you need to limit your count to 1.

0x49D1 Over a year ago

Sure ;) used top(1) before hes told. Thanks again.

Collectives™ on Stack Overflow

Get result with maximum values in multiple columns at once in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related