How to return specific rows from a query

Question

Post reply

How to return specific rows from a query

jmcgrail

Valued Member

Points: 62
More actions
September 16, 2016 at 7:45 am

#313674

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)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

"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

andrew gothard SSChampion Points: 12316 More actions · Answer 3

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.

drew.allen SSC Guru Points: 77005 More actions · Answer 4

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

drew.allen SSC Guru Points: 77005 More actions · Answer 5

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

jmcgrail Valued Member Points: 62 More actions · Answer 6

jmcgrail

Valued Member

Points: 62

September 16, 2016 at 8:44 am

#1901373

I used option 2, the derived table. Thank you.