April 5, 2013 at 10:53 am
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.
April 5, 2013 at 11:13 am
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
)
)
April 5, 2013 at 11:19 am
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
April 5, 2013 at 11:40 am
Nice one Lynn, beats my multiple max operators hands down :w00t:
April 5, 2013 at 3:51 pm
;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
April 5, 2013 at 3:56 pm
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().
April 5, 2013 at 5:51 pm
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.
April 5, 2013 at 6:33 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply