What would be the cheapest query for this?

Question

Post reply

What would be the cheapest query for this?

virgilrucsandescu

SSCrazy

Points: 2457
More actions
August 6, 2009 at 8:59 am

#135184

If I have a table / view like this :
ID ATTR1 ATTR2 ... ATTRn
--------------------------
id1 , attribute fields line 1
id1 , attribute fields line 2
id2 , attribute fields line 3
id2 , attribute fields line 4
........
........
idn , attribute fields line j
idn , attribute fields line j+1
idn , attribute fields line j+2
what would be the cheapest query to show only one line per id ? And I really don’t care which would be that line, all I care for this is performance… (the source table is quite large...)
The queries I have tried are slow, I am pretty sure I am overlooking a very simple & unexpensive way of doing this ... Please help!
Thank you very much!
Virgil Rucsandescu

Viewing 3 posts - 1 through 2 (of 2 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can do that a few different ways.

Try this:

;with CTE as

(select

Row_Number() over (partition by ID order by Attr1) as Row,

ID,

Attr1,

... more Attr columns ....

from dbo.MyTable)

select *

from CTE

where Row = 1;

That usually works pretty well. You can also use min/max functions and an aggregate query, but that won't necessarily get you data from the same row.

You'll have to fill in the column names you want, where I have "... more Attr columns ....", of course.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

virgilrucsandescu (8/6/2009)
If I have a table / view like this :
ID ATTR1 ATTR2 ... ATTRn
--------------------------
id1 , attribute fields line 1
id1 , attribute fields line 2
id2 , attribute fields line 3
id2 , attribute fields line 4
........
........
idn , attribute fields line j
idn , attribute fields line j+1
idn , attribute fields line j+2
what would be the cheapest query to show only one line per id ? And I really don’t care which would be that line, all I care for this is performance… (the source table is quite large...)
The queries I have tried are slow, I am pretty sure I am overlooking a very simple & unexpensive way of doing this ... Please help!
Thank you very much!
Virgil Rucsandescu

The following will work...

http://www.sqlservercentral.com/articles/Crosstab/65048/

... instead of using SUM or COUNT, use MAX.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)