Geek asks and answers

Find all results for 2nd to most recent date.

I have a history table that contains an estimate for each group by date (PC - group, date). What is an SQL query that can get ratings for all groups for the second most recent date?

ETA: dates are the same for each group (each point is entered in the history table for each group at the same time).

+3
source share
4 answers
select *
from ScoreHistory sc1
where exists
(
    select GroupId, max(ScoreDate) RecentScoreDate
    from ScoreHistory sc2
    where not exists
    (
        select GroupId, max(ScoreDate) RecentScoreDate
        from ScoreHistory sc3
        group by GroupId
        having GroupId = sc2.GroupId and max(ScoreDate) = sc2.ScoreDate
    )
    group by GroupId
    having GroupId = sc1.GroupId and max(ScoreDate) = sc1.ScoreDate
)

Setup:

create table ScoreHistory(GroupId int, ScoreDate datetime)

insert ScoreHistory
    select 1, '2011-06-14' union all
    select 1, '2011-06-15' union all
    select 1, '2011-06-16' union all
    select 2, '2011-06-15' union all
    select 2, '2011-06-16' union all
    select 2, '2011-06-17'

The query looks as simple as shown below for MS SQL 2005+

;with cte
as
(
    select *, row_number() over(partition by GroupId order by ScoreDate desc) RowNumber
    from ScoreHistory
)
select *
from cte
where RowNumber = 2
+4
source

You need two units

  • get maximum dates per group
  • Get maximum dates for a group that are less than the dates from step 1
  • join an account from this aggregate

Sort of

SELECT
    Group, Date, Score
FROM
    ( ..2nd max date per group
    SELECT
       Group, MAX(Date) AS TakeMe
    FROM
        ( --max date per group
        SELECT
           Group, MAX(Date) AS IgnoreMe
        FROM
           MyTable
        GROUP BY
           Group
        ) ex
        JOIN
        MyTable M ON ex.Group = M.Group AND ex.IgnoreMe > M.Date
    GROUP BY
        M.Group
    ) inc
    JOIN
    MyTable M2 ON inc.Group = M2.Group AND inc.TakeMe = M2.Date

This is much simpler on SQL Server 2005 with ROW_NUMBER () ...

+3

Try it. First I try to get a TOP 2 DISTINCTDates Desc that will work if you only use dates, not dates. Then turn this table over and get TOP 1and using this result as the second most recent date to get group ratings.

SELECT *
FROM YourTable
INNER JOIN 
(SELECT TOP 1 x.[date]
FROM
    (SELECT TOP 2 DISTINCT [date]
    FROM YourTable
    ORDER BY [date] DESC) AS x
ORDER BY [date] ASC) AS y
ON y.[date] = YourTable.[date]

I think it may be necessary WHERE y.date = YourTable.date, but I'm not sure

+2
source 
SELECT *
FROM tblScore
WHERE EXISTS
(
    SELECT NULL
    FROM tblScore as tblOuter
    WHERE NOT EXISTS
    (
        SELECT NULL
        FROM tblScore As tblInner
        WHERE tblInner.[group] = tblOuter.[group]
        GROUP BY [group]
        HAVING MAX(tblInner.[date]) = tblOuter.[date]
    ) 
    AND tblOuter.[group] = tblScore.[group]
    GROUP BY [group]
    HAVING MAX(tblOuter.[date]) = tblScore.[date]
)
+2
source

More articles:


All Articles