September 16, 2016 at 7:45 am
My query returns multiple records for each VisitID. I only want row number 1 and 2, based on ApgarOrder, for my final results. Is there a way to do this without using a cte or temp table?
SELECTVisitID,
InstanceID,
ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY InstanceID) as ApgarOrder,
ValueInfo
FROM #Test
create table #Test
(
VisitID varchar(30),
InstanceID varchar(100),
ValueInfo int
)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000031269581}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000031269581}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000031269581}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000054011490}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000054011490}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160802155134536','{NB.APGAR|S|1969335.000000054011490}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160803054625870','{NB.APGAR|S|1975698.000000131279159}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160803054625870','{NB.APGAR|S|1975698.000000131279159}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160803054625870','{NB.APGAR|S|1975698.000000137411621}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160803054625870','{NB.APGAR|S|1975698.000000137411621}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804011755135','{NB.APGAR|S|1992346.000000238551251}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804011755135','{NB.APGAR|S|1992346.000000238551251}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804011755135','{NB.APGAR|S|1992346.000000258173785}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804011755135','{NB.APGAR|S|1992346.000000258173785}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804230938997','{NB.APGAR|S|2013768.000000011100979}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804230938997','{NB.APGAR|S|2013768.000000011100979}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804230938997','{NB.APGAR|S|2013768.000000028930552}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160804230938997','{NB.APGAR|S|2013768.000000028930552}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000498018663}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000498018663}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000498018663}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000556004370}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000556004370}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805064601713','{NB.APGAR|S|2013738.000000556004370}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805190000742','{NB.APGAR|S|2062895.000000978840571}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160805190000742','{NB.APGAR|S|2062895.000000997354769}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808033259104','{NB.APGAR|S|2069263.000000625999262}',6)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808033259104','{NB.APGAR|S|2069263.000000625999262}',6)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808033259104','{NB.APGAR|S|2069263.000000694647002}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808033259104','{NB.APGAR|S|2069263.000000694647002}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543569}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543569}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543569}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543657}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543657}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160808191752137','{NB.APGAR|S|2064243.000000140543657}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160813093949684','{NB.APGAR|S|2147740.000000040030265}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160813093949684','{NB.APGAR|S|2147740.000000040030265}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160813093949684','{NB.APGAR|S|2147740.000000054130337}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160813093949684','{NB.APGAR|S|2147740.000000054130337}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000252725567}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000252725567}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000252725567}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000269067265}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000269067265}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160814094822302','{NB.APGAR|S|2181609.000000269067265}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160815174127500','{NB.APGAR|S|2181653.000000129653726}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160815174127500','{NB.APGAR|S|2181653.000000141208650}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000046490073}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000046490073}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000046490073}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000060540316}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000060540316}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F0-B20160816051848289','{NB.APGAR|S|2179274.000000060540316}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160731165428243','{NB.APGAR|S|1940586.000000084739641}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160731165428243','{NB.APGAR|S|1940586.000000114604859}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859565}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859565}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859565}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859655}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859655}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801054819414','{NB.APGAR|S|1938867.000000550859655}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841281}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841281}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841281}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841281}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841350}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841350}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841350}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801074227405','{NB.APGAR|S|1942821.000000599841350}',8)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000011260354}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000011260354}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000011260354}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000011260354}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000011260354}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000014554254}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000014554254}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000014554254}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000014554254}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080023221','{NB.APGAR|S|1942287.000000014554254}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857849}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857849}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857849}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857849}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857849}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857918}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857918}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857918}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857918}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801080606546','{NB.APGAR|S|1943537.000000667857918}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000070038374}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000070038374}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000070038374}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000070038374}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000079003551}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000079003551}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000079003551}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801102457405','{NB.APGAR|S|1952683.000000079003551}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801113421165','{NB.APGAR|S|1952124.000000090052166}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801113421165','{NB.APGAR|S|1952124.000000090052166}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801113421165','{NB.APGAR|S|1952124.000000099877862}',9)
insert into #Test(VisitID, InstanceID, ValueInfo) values('F1-B20160801113421165','{NB.APGAR|S|1952124.000000099877862}',9)
September 16, 2016 at 7:53 am
Why not using a CTE such as:
WITH cte AS (SELECTVisitID,
InstanceID,
ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY InstanceID) as ApgarOrder,
ValueInfo
FROM #Test
)
SELECT visitid, instanceid, apgarorder, valueinfo FROM cte
WHERE apgarorder <= 2;
Well, if you insist, this doesn't use a CTE explicitly, but a derived table:
SELECT a.VisitID ,
a.InstanceID ,
a.ApgarOrder ,
a.ValueInfo FROM (SELECTVisitID,
InstanceID,
ROW_NUMBER() OVER (PARTITION BY VisitID ORDER BY InstanceID) as ApgarOrder,
ValueInfo
FROM #Test
) a
WHERE a.apgarorder <= 2;
However, the query plan from these statements is identical (at least, on my SQL2014 instance it is...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 16, 2016 at 7:57 am
"Is there a way to do this without using a cte or temp table"...please explain why you have these restrictions
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 16, 2016 at 8:02 am
You're actually quite close, you just need to put your query into a derived table and filter on the ranked column
SELECT
VisitID, InstanceID, ValueInfo
FROM
(
SELECT
VisitID, InstanceID, ValueInfo,
Row_Number() OVER(PARTITION BY VisitID ORDER BY InstanceID) AS ApgarOrder
FROM #Test
) rs
WHERE rs.ApgarOrder < 3
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 16, 2016 at 8:13 am
The WHERE clause is evaluated before the SELECT clause (which is where the ROW_NUMBER is defined), therefore the WHERE cannot reference the ROW_NUMBER in the same (sub)query where the ROW_NUMBER is defined. You have to split this up by using a CTE/derived table or storing the results and then querying the results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 16, 2016 at 8:25 am
There is a cheat if you only want to return the first row within each partition, but it uses an additional sort, so it's actually more expensive than using the CTE/derived table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 16, 2016 at 8:44 am
I used option 2, the derived table. Thank you.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy