CODEWITHSUNDEEP
sqlsql-serverselect
santhosha
santhosha

Reputation: 439

Designating Maximum record in table using SQL

Could anyone help me to achieve this using SQL. I am a very basic user in SQL and tried doing it with GROUP BY in combination with HAVING Clause but it was filtering the records which i dot want to.

All I a want to acieve here is the Relative month column. Wherever there is maximum MonthNUmber the correpsponding column should be designated as "Current Month"

enter image description here

Upvotes: 3

Views: 58

Answers (4)

Mureinik
Mureinik

Reputation: 311978

@Tim Biegeleisen has the right idea, although I think using the windowing variant of max would be much more elegant (and should, in theory, also be faster, although you should always benchmark all the options instead of just accepting such claims).

SELECT ID, 
       EmpName, 
       MonthNumber,
       CASE MonthNumber WHEN MAX(MonthNumber) OVER () THEN 'Current' END
from   mytable;

Upvotes: 2

Wassim Gr
Wassim Gr

Reputation: 588

SELECT RelativeMonth FROM table WHERE MonthNumber = (SELECT Max(MonthNumber) FROM table)

Upvotes: 0

Sifat Haque
Sifat Haque

Reputation: 6067

As i understand may be MAX will serve your purpose

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522302

You could use a subquery to do this:

SELECT ID,
       EmpName,
       MonthNumber,
       CASE WHEN MonthNumber = (SELECT MAX(MonthNumber) FROM yourTable)
            THEN 'Current' ELSE NULL END AS RelativeMonth
FROM yourTable

Upvotes: 3

Related Questions