Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve Latest and Previous Record Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 284, Visits: 786
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.
Post #1439367
Posted Friday, April 5, 2013 11:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 348, Visits: 2,498
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
)
)

Post #1439377
Posted Friday, April 5, 2013 11:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
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





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)
Post #1439379
Posted Friday, April 5, 2013 11:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 348, Visits: 2,498
Nice one Lynn, beats my multiple max operators hands down
Post #1439388
Posted Friday, April 5, 2013 3:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
;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


Post #1439516
Posted Friday, April 5, 2013 3:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
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().



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)
Post #1439519
Posted Friday, April 5, 2013 5:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1439538
Posted Friday, April 5, 2013 6:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 20,734, Visits: 32,497
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.



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)
Post #1439541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse