Selecting the top row from a group of records.

Question

Selecting the top row from a group of records.

dndaughtery

SSChampion

Points: 11290
More actions
December 3, 2015 at 7:18 am

#306854

Hey guys, I have a need to query a table grouped by Col_A, Col_B, Col_C, Col_D and return only one record because each has a primary key that I also need returned but I don't want to group on the primary key. Would this work:
Select PrimeKey, A.Col_A, A.Col_B, A.Col_C, A.Col_D, B.Col_A, RANK() Over(Partition BY A.Col_A, A.Col_B, A.Col_C, A.Col_D) num
from Table_A A inner join Table_B B on A.PrimeKey = B.PrimeKey
Where num = 1

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

dndaughtery (12/3/2015)
Hey guys, I have a need to query a table grouped by Col_A, Col_B, Col_C, Col_D and return only one record because each has a primary key that I also need returned but I don't want to group on the primary key. Would this work:
Select PrimeKey, A.Col_A, A.Col_B, A.Col_C, A.Col_D, B.Col_A, RANK() Over(Partition BY A.Col_A, A.Col_B, A.Col_C, A.Col_D) num
from Table_A A inner join Table_B B on A.PrimeKey = B.PrimeKey
Where num = 1

Did you try it? Did you discover that won't work? You would have to use a subquery or cte to reference the window function in the where clause.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 2

did you try this ?

Select PrimeKey, Col_A, Col_B, Col_C, Col_D, Col_A_B

from ( Select PrimeKey, A.Col_A, A.Col_B, A.Col_C, A.Col_D, B.Col_A AS Col_A_B,

RANK() Over(Partition BY A.Col_A, A.Col_B, A.Col_C, A.Col_D, order by A.Col_A, A.Col_B, A.Col_C, A.Col_D) num

from Table_A A inner join Table_B B on A.PrimeKey = B.PrimeKey

) A

Where num = 1

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

dndaughtery SSChampion Points: 11290 More actions · Answer 3

Yeah, was trying to save time loading the CTE but guess I can't get around it

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

dndaughtery (12/3/2015)
Yeah, was trying to save time loading the CTE but guess I can't get around it

Save time? It is like 3 seconds to wrap that in a cte. Certainly way faster than a forum post. 😉

with mycte as

(

Select PrimeKey, A.Col_A, A.Col_B, A.Col_C, A.Col_D, B.Col_A, RANK() Over(Partition BY A.Col_A, A.Col_B, A.Col_C, A.Col_D) num

from Table_A A inner join Table_B B on A.PrimeKey = B.PrimeKey

)

select *

from mycte

where num = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dndaughtery SSChampion Points: 11290 More actions · Answer 5

Well, that's not the exact query I'm working on. Scaled it down quite a bit. My query has 80 columns with several case statements etc that returns 1 million records. Trying to update an old process that was loading the data into an adodb recordset then grouping it to get the top 1 etc.

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 6

dndaughtery (12/3/2015)
Well, that's not the exact query I'm working on. Scaled it down quite a bit. My query has 80 columns with several case statements etc that returns 1 million records. Trying to update an old process that was loading the data into an adodb recordset then grouping it to get the top 1 etc.

and that's what should have been mentioned in your OP in the first place !

btw as with a nested table expression ( as in my first reply ) a common table expression supports every t-sql query.

When dealing with a large set, this may put pressure on tempdb, so that may need some tuning too !

Chances are, you may need to put it into a regular #temptable to make it all perform well enough.

( you then also have the advantage of indexing that table if needed )