Need to get last two hearing dates in table

acicchillo · #1 (**permalink**) 02-07-13, 14:21

Hi all -
Another newbie question.
We store all hearing dates in our db. Most cases have many hearings, but I only want to get the last two dates from the hearing table.
I can get the very last hearing date by using Max() and I can get the first by using Min() but how do I get the next previous date?
Thanks again for your help
Andy

imex · #2 (**permalink**) 02-07-13, 15:10

Hi,

Try something like this:

Code:

with CTE_H as
(
    select 
        t.*, 
        ROW_NUMBER() OVER(ORDER BY t.HearingDate DESC) as RowNum
    from MyTable as t
)

select * from CTE_H
where RowNum <= 2

Hope this helps.

Pat Phelan · #3 (**permalink**) 02-07-13, 15:49

I'd suggest something a bit more complicated, but flexible:

Code:

CREATE TABLE #hearings (
   caseNumber   INT
,  hearingDate  DATETIME
   )

INSERT INTO #hearings (caseNumber, hearingDate)
   VALUES (1, '2000-01-01'), (2, '2000-02-02'), (2, '2001-02-02')
,  (3, '2000-03-03'), (3, '2001-03-03'), (3, '2002-03-03');


WITH cte (caseNumber, hearingDate, r) AS
(SELECT caseNumber, hearingDate, row_number() OVER
   (PARTITION BY caseNumber ORDER BY hearingDate DESC) AS r
   FROM #hearings)
SELECT caseNumber, hearingDate
   FROM cte
   WHERE  r <= 2
   ORDER BY caseNumber, hearingDate

-PatP