CODEWITHSUNDEEP
sqlsql-server
mao
mao

Reputation: 69

SQL Server Max() function

I have a column named as Quarter in which data is as below:

Quarter
--------
Q3-2017
Q2-2017
Q1-2017
Q4-2016
Q3-2016
Q2-2016
Q1-2016
Q1-2018

I want to find max() from above. How should I proceed.

When I tried with MAX() function it is giving me output as Q4-2017.

Upvotes: 0

Views: 64

Answers (3)

MarcM
MarcM

Reputation: 2251

Split quarter field into year (numeric!) and quarter (string), and order by at your taste. Get first row (top 1) to get only the max value:

SELECT TOP 1 quarter, 
CAST(SUBSTRING(quarter, 4, 4) AS INTEGER) AS y,  
SUBSTRING(quarter, 1, 2) AS q FROM quarter
ORDER BY y desc, q desc

Upvotes: 0

Nilam Desai
Nilam Desai

Reputation: 7

You can use query like : SELECT quarter from QuarterTable where right(quarter,4) = (select max(right(quarter,4) from QuarterTable);

Upvotes: 0

Tanner
Tanner

Reputation: 22733

This is happening because it's giving you the max of the column which is in a string format. It's ordering it Alpha-numerically and that's the max value when you sort the data. If you want to order it as you expect, you need to use some string manipulation to break the values down for ordering.

CREATE TABLE #quarters
    (
        [quarter] NVARCHAR(10)
    );

INSERT INTO #quarters ( quarter )
VALUES ( 'Q3-2017' ) ,
       ( 'Q2-2017' ) ,
       ( 'Q1-2017' ) ,
       ( 'Q4-2016' ) ,
       ( 'Q3-2016' ) ,
       ( 'Q2-2016' ) ,
       ( 'Q1-2016' ) ,
       ( 'Q1-2018' );

SELECT   q.quarter Original ,
         CAST(RIGHT(q.quarter, 4) AS INT) AS TheYear , -- extracts the year
         CAST(SUBSTRING(q.quarter, 2, 1) AS INT) AS TheQuarter -- extracts the quarter
FROM     #quarters AS q
ORDER BY CAST(RIGHT(q.quarter, 4) AS INT) DESC ,
         CAST(SUBSTRING(q.quarter, 2, 1) AS INT) DESC;

DROP TABLE #quarters;

Produces:

Original   TheYear     TheQuarter
---------- ----------- -----------
Q1-2018    2018        1
Q3-2017    2017        3
Q2-2017    2017        2
Q1-2017    2017        1
Q4-2016    2016        4
Q3-2016    2016        3
Q2-2016    2016        2
Q1-2016    2016        1

The above solution would also work without the casting: CAST((XXX) AS INT), but it's safer to do that in case an unexpected value appears.

And to get the top value, use TOP:

SELECT TOP 1 q.quarter Original 
FROM     #quarters AS q
ORDER BY CAST(RIGHT(q.quarter, 4) AS INT) DESC ,
     CAST(SUBSTRING(q.quarter, 2, 1) AS INT) DESC;

Upvotes: 3

Related Questions