Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    8

    Need to get last two hearing dates in table

    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

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2013
    Posts
    8
    Thanks so much for your replies. They were a big help
    Andy

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    793
    SELECT TOP 2 caseNumber, hearingDate
    FROM HEARINGS
    ORDER BY hearingDate DESC

    -- less code, same results

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,948
    Quote Originally Posted by corncrowe View Post
    -- less code, same results
    Not with my sample data at least.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •