Below is the query result of an attempt to get top 1 for each group.
The twist is coming from the column epTypeId. There are two rows for each aId (87, 88, 89), the desired result is in the second table, which only has one row per aId, the one that has the most recent time and date.
Current Result:
fdId
aId
obDate
fdVal
epTypeId
9676
87
2016-09-22 11:03:55.200
0
50
9675
87
2016-09-22 11:03:50.383
1
44
9844
89
2016-09-22 13:45:02.807
0
50
9843
89
2016-09-22 13:44:58.470
1
44
9064
90
2016-09-22 02:37:17.230
0
50
9063
90
2016-09-22 02:37:15.270
1
44
Desired Result:
fdId
aId
obDate
fdVal
epTypeId
9676
87
2016-09-22 11:03:55.200
0
50
9843
89
2016-09-22 13:44:58.470
1
44
9064
90
2016-09-22 02:37:17.230
0
50
The query:
SELECT MAX(fdId) AS fdId, aId, MAX(obDate) AS obDate, fdVal, epTypeId
FROM fData fd
WHERE epTypeId IN (44, 50)
GROUP BY aId, fdVal, epTypeId
Will "mark as answered" if your answers help me. Will you do the same?
;with mycte as (SELECT fdId, aId, obDate, fdVal, epTypeId
, row_number() Over(Partition by aID Order by obDate Desc) rn
FROM fData fd
WHERE epTypeId IN (44, 50)
)
Select fdId, aId, obDate, fdVal, epTypeId
from mycte
WHERE rn=1
Can you check below query? I haven't tested it but it should work:
SELECT fdId, aId, obDate, fdVal, epTypeId
FROM fData f
INNER JOIN
(
SELECT aId, MAX(obDate) AS obDate
FROM fData fd
WHERE epTypeId IN (44, 50)
GROUP BY aId
) a
ON f.aId = a.aId AND f.obDate = a.obDate
Thanks, Sumit.
/**** Please remember to "Mark as Answer" the responses that resolved your issue. ****/
Contributor
2362 Points
2352 Posts
top 1 for each group with a twist
Sep 23, 2016 05:24 PM|wavemaster|LINK
Below is the query result of an attempt to get top 1 for each group.
The twist is coming from the column epTypeId. There are two rows for each aId (87, 88, 89), the desired result is in the second table, which only has one row per aId, the one that has the most recent time and date.
Current Result:
fdId
aId
obDate
fdVal
epTypeId
9676
87
2016-09-22 11:03:55.200
0
50
9675
87
2016-09-22 11:03:50.383
1
44
9844
89
2016-09-22 13:45:02.807
0
50
9843
89
2016-09-22 13:44:58.470
1
44
9064
90
2016-09-22 02:37:17.230
0
50
9063
90
2016-09-22 02:37:15.270
1
44
Desired Result:
fdId
aId
obDate
fdVal
epTypeId
9676
87
2016-09-22 11:03:55.200
0
50
9843
89
2016-09-22 13:44:58.470
1
44
9064
90
2016-09-22 02:37:17.230
0
50
The query:
SELECT MAX(fdId) AS fdId, aId, MAX(obDate) AS obDate, fdVal, epTypeId
FROM fData fd
WHERE epTypeId IN (44, 50)
GROUP BY aId, fdVal, epTypeId
All-Star
122732 Points
9858 Posts
Moderator
Re: top 1 for each group with a twist
Sep 23, 2016 05:38 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Participant
1060 Points
346 Posts
Re: top 1 for each group with a twist
Sep 23, 2016 05:39 PM|Sumit.Pokhriyal|LINK
Can you check below query? I haven't tested it but it should work:
SELECT fdId, aId, obDate, fdVal, epTypeId
FROM fData f
INNER JOIN
(
SELECT aId, MAX(obDate) AS obDate
FROM fData fd
WHERE epTypeId IN (44, 50)
GROUP BY aId
) a
ON f.aId = a.aId AND f.obDate = a.obDate
/**** Please remember to "Mark as Answer" the responses that resolved your issue. ****/
Contributor
2362 Points
2352 Posts
Re: top 1 for each group with a twist
Sep 24, 2016 11:22 AM|wavemaster|LINK
Both queries work.
Thanks.