CODEWITHSUNDEEP
sqlsql-servert-sql
Roger
Roger

Reputation: 1631

How to select the equivalent row for another column of the max(column) in group by in SQL Server

I need to make a change in the Sql below to make CreatedOn return the selected record of the Max(Value). You can observe the -- todo line.

Should return: 2/01/2015 and 8/01/2015 as you can see in Query Result, but the Max(CreatedOn) will select the max and not the referent record of the Max(Value).

Sql

SET DATEFIRST 1
SELECT 
   CONCAT(DATEPART(YEAR, CreatedOn),DATEPART(WEEK, CreatedOn)) Week, 
   MAX(CreatedOn) CreatedOn, -- todo: this should return 2/01/2015 and 8/01/2015
   MAX(Value) AS MaxValue
FROM Table1
GROUP BY CONCAT(DATEPART(YEAR, CreatedOn),DATEPART(WEEK, CreatedOn))

Table 1:

╔════╦═══════════╦═══════╗
║ Id ║ CreatedOn ║ Value ║
╠════╬═══════════╬═══════╣
║  1 ║ 1/01/2015 ║     1 ║
║  2 ║ 2/01/2015 ║     2 ║
║  3 ║ 8/01/2015 ║     4 ║
║  4 ║ 9/01/2015 ║     2 ║
╚════╩═══════════╩═══════╝

Query Result:

╔════════╦═══════════╦══════════╗
║  Week  ║ CreatedOn ║ MaxValue ║
╠════════╬═══════════╬══════════╣
║ 2015 1 ║ 2/01/2015 ║        2 ║
║ 2015 2 ║ 8/01/2015 ║        4 ║
╚════════╩═══════════╩══════════╝

*Edited: I need to return 8/01/2015 because it is the correspondent row of the MaxValue (4).

Upvotes: 3

Views: 91

Answers (2)

StuartLC
StuartLC

Reputation: 107237

You can use the ROW_NUMBER() over a partition of each week (PARTITION BY Week), ordering by the descending value (ORDER BY Value DESC) to 'rank' each record within the week. Selecting the row with the highest value in each week is then simply the top ranked row in each partition (WHERE Rnk = 1). I've used CTEs to prevent the repetition of the Week calculation.

WITH Weeks AS
(
   SELECT CONCAT(DATEPART(YEAR, CreatedOn),DATEPART(WEEK, CreatedOn)) Week,
          Id, CreatedOn, Value
   FROM Table1
),
Ranked As
(
  SELECT Week, CreatedOn, Value,
          ROW_NUMBER() OVER (PARTITION BY Week ORDER BY Value DESC) Rnk
  FROM Weeks
)
SELECT Week, CreatedOn, Value
FROM Ranked
WHERE Rnk = 1;

SqlFiddle here

Upvotes: 4

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Your query is correct, however there is a problem with the date format. You are reading your dates as dd/mm/yyyy while DB is interpreting them as mm/dd/yyyy.

So a quick fix is, use proper format while inserting values like 

insert into Table1 ( id ,  CreatedOn,  value)
values (1 , '01/01/2015' ,    1 )
insert into Table1 ( id ,  CreatedOn,  value)
values (2 , '01/02/2015' ,     2 )
insert into Table1 ( id ,  CreatedOn,  value)
values (3 , '01/09/2015' ,     4 )
insert into Table1 ( id ,  CreatedOn,  value)
values (4 , '01/08/2015' ,     2)

I tried in this worked. Let me know if you need SQLFiddle of the same.

Upvotes: 2

Related Questions