SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieve Latest and Previous Record


Retrieve Latest and Previous Record

Author
Message
bpowers
bpowers
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 Visits: 960
Any idea how to return the latest and previous record (TOP 2) from a table and have them on the same line, side-by-side. I tried using a CTE and partitioning, but I couldn't figue out how to union two CTE's. Don't know if that is even possible. Any help or ideas will be appreciated.
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 5527
LOL queue the experts to post a more elegant solution!

Anyways, I'm just throwing this out there for discussion's sake, make sure you test any solution you develop for suitability for your application.


USE [CRAP_DISPOSABLE_DATABASE_I_HAVE]
GO


CREATE TABLE [dbo].[TEST_FOR_DEMO](
[whateverkey] [int] NULL,
[rowdate] [datetime] NULL,
[otherinfo] [varchar](100) NULL
) ON [PRIMARY]

GO

INSERT INTO TEST_FOR_DEMO
(
whateverkey,
rowdate,
otherinfo
)
SELECT 1, '2013-01-01','fred'
UNION
SELECT 2, '2013-01-05','ralph'
UNION
SELECT 3, '2012-12-31','jane'
UNION
SELECT 4, '2012-12-15','suzie'


SELECT TOP 1 latest.whateverkey whateverkey_latest,
latest.rowdate rowdate_latest,
latest.otherinfo otherinfo_latest,
next_latest.whateverkey whateverkey_next_latest,
next_latest.rowdate rowdate_next_latest,
next_latest.otherinfo otherinfo_next_latest
FROM
TEST_FOR_DEMO latest
CROSS JOIN
TEST_FOR_DEMO next_latest
WHERE
latest.rowdate =
(
SELECT MAX(rowdate)
FROM TEST_FOR_DEMO
)
AND
next_latest.rowdate =
(SELECT MAX(rowdate)
FROM TEST_FOR_DEMO
WHERE rowdate <>
(SELECT MAX(rowdate)
FROM TEST_FOR_DEMO
)
)



to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40376 Visits: 38567
I came up with this using a very simplistic setup:



declare @TestTab table(
tid int identity(1,1),
tdata varchar(10)
);

insert into @TestTab
values ('a'),('b'),('c'),('d');

with Top2 as (
select top 2 tid, tdata from @TestTab order by tid desc
), LastPrev as (
select
max(tid) as LastRec,
min(tid) as PrevRec
from
Top2
)
--select * from LastPrev
select * from
(select
*
from
Top2
where
tid = (select LastRec from LastPrev)
) dt
cross apply
(select
*
from
Top2
where
tid = (select PrevRec from LastPrev)
) dt1




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 5527
Nice one Lynn, beats my multiple max operators hands down w00t

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1721

;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'2013-01-01','fred'),
(2,'2013-01-05','ralph'),
(3,'2012-12-31','jane'),
(4,'2012-12-15','suzie')
) DATA (ID,EndDate,Name))
,
cteRank AS
(
SELECT
--RANK() OVER (ORDER BY EndDate DESC) AS [Rank]
RANK() OVER (ORDER BY EndDate DESC, ID DESC) AS [Rank] -- edited to add a tie-breaker
,ID
,EndDate
,Name
FROM
sampledata
)
SELECT
*
FROM
cteRank c1
CROSS APPLY
cteRank c2
WHERE
c1.[Rank] = 1
AND c2.[Rank] = 2



Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40376 Visits: 38567
Steven Willis (4/5/2013)

;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'2013-01-01','fred'),
(2,'2013-01-05','ralph'),
(3,'2012-12-31','jane'),
(4,'2012-12-15','suzie')
) DATA (ID,EndDate,Name))
,
cteRank AS
(
SELECT
RANK() OVER (ORDER BY EndDate DESC) AS [Rank]
,ID
,EndDate
,Name
FROM
sampledata
)
SELECT
*
FROM
cteRank c1
CROSS APPLY
cteRank c2
WHERE
c1.[Rank] = 1
AND c2.[Rank] = 2




If you have duplicate dates, this fails. You will want to change RANK() to ROW_NUMBER().

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1721
Lynn Pettis (4/5/2013)
If you have duplicate dates, this fails. You will want to change RANK() to ROW_NUMBER().

Good catch Lynn. But I don't think just changing to ROW_NUMBER fixes the tie-break problem. It just returns one row and who knows what the tie-breaker is? So whether using RANK, ROW_NUMBER, or perhaps DENSE-RANK, I should have added a tie-breaker to the ORDER BY like this:


RANK() OVER (ORDER BY EndDate DESC, ID DESC) AS [Rank]



This would give the tie-break to the most recently added row...but other ways of breaking the tie might be better such as a full DATETIME2 date.

 
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40376 Visits: 38567
Steven Willis (4/5/2013)
Lynn Pettis (4/5/2013)
If you have duplicate dates, this fails. You will want to change RANK() to ROW_NUMBER().

Good catch Lynn. But I don't think just changing to ROW_NUMBER fixes the tie-break problem. It just returns one row and who knows what the tie-breaker is? So whether using RANK, ROW_NUMBER, or perhaps DENSE-RANK, I should have added a tie-breaker to the ORDER BY like this:


RANK() OVER (ORDER BY EndDate DESC, ID DESC) AS [Rank]



This would give the tie-break to the most recently added row...but other ways of breaking the tie might be better such as a full DATETIME2 date.

 


ROW_NUMBER will work fine as the requirement really only needs the top 2 records. If there is a tie you still only want the top 2. DENSE_RANK didn't work either with the limited data sample. Give it a try with a tie on the latest dates.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search