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.
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;
You can use query like : SELECT quarter from QuarterTable where right(quarter,4) = (select max(right(quarter,4) from QuarterTable);
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
select MAX(Quarter) over (order by right(Quarter,4), RIGHT(LEFT(Quarter, 2), 1)) from YourTable... which will first order by year, then quarter number