If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Need to get last two hearing dates in table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2012
Posts: 211
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.
Reply With Quote
  #3 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,743
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 8
Thanks so much for your replies. They were a big help
Andy
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Location: Dallas, Texas
Posts: 784
SELECT TOP 2 caseNumber, hearingDate
FROM HEARINGS
ORDER BY hearingDate DESC

-- less code, same results
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,743
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.
Reply With Quote
Reply

Tags
sql 2008 r2

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On