How to select an equivalent row for another max (column) column in a group by SQL Server
I need to make changes in Sql below to make CreatedOn return the selected Max (Value) record. You can see the line - todo .
Should return: 2/01/2015 and 8/01/2015 as you can see in the query result but Max (CreatedOn) will select the max record, not the reference Max record . (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 this is the corresponding MaxValue (4) string.
source to share
You can use ROW_NUMBER()
( PARTITION BY Week
) above each week's section , in descending order ( ORDER BY Value DESC
), to "rank" each entry for the week. Selecting the row with the highest value each week is just the top ranked row in each section ( WHERE Rnk = 1
). I used CTE to prevent repeating the calculation of the week.
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;
source to share
Your request is correct, however there is a problem with the date format. You read your dates as dd / mm / yyyy while DB interprets them as mm / dd / yyyy.
So quick fix, use the correct format by 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 to work with this. Let me know if you need SQLFiddle.
source to share