locked
top 1 for each group with a twist RRS feed

  • Question

  • User379720387 posted

    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

    Friday, September 23, 2016 5:24 PM

Answers

  • User77042963 posted
    ;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
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:38 PM
  • User-1672470423 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:39 PM

All replies

  • User77042963 posted
    ;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
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:38 PM
  • User-1672470423 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 23, 2016 5:39 PM
  • User379720387 posted

    Both queries work.

    Thanks.

    Saturday, September 24, 2016 11:22 AM