Get top 1 row in group

Question

Get top 1 row in group

vee_jess

SSC Eights!

Points: 984
More actions
November 18, 2014 at 2:23 pm

#397655

Hi,
Please help me write the T-SQL to get the top 1 row in the group. Thanks
Sample data
Stack RefID Name
DM1-102Australia Post
DM2-102Australia Post
DM6-102Australia Post
DM3-102Australia Post
DM4-102Australia Post
DM5-102Australia Post
DMASOS-102StarTrack Express
DM1-83NEGATIVE DPD Netherlands DO NOT USE
DM2-83NEGATIVE DPD Netherlands DO NOT USE
DM3-83NEGATIVE DPD Netherlands DO NOT USE
DM4-83NEGATIVE DPD Netherlands DO NOT USE
DM5-83NEGATIVE DPD Netherlands DO NOT USE
DM6-83NEGATIVE DPD Netherlands DO NOT USE
DM1-78NEGATIVE DPD Germany DO NOT USE
DM2-78NEGATIVE DPD Germany DO NOT USE
DM3-78NEGATIVE DPD Germany DO NOT USE
DM4-78NEGATIVE DPD Germany DO NOT USE
DM5-78NEGATIVE DPD Germany DO NOT USE
DM6-78NEGATIVE DPD Germany DO NOT USE
DM1-69NEGATIVE DPD Belgium DO NOT USE
DM2-69NEGATIVE DPD Belgium DO NOT USE
DM3-69NEGATIVE DPD Belgium DO NOT USE
DM4-69NEGATIVE DPD Belgium DO NOT USE
DM5-69NEGATIVE DPD Belgium DO NOT USE
DMMS-69NEGATIVE DPD Belgium DO NOT USE
DM6-69NEGATIVE DPD Belgium DO NOT USE
DM1-65NEGATIVE wnDirect Italy DO NOT USE
DM2-65NEGATIVE wnDirect Italy DO NOT USE
DM3-65NEGATIVE wnDirect Italy DO NOT USE
DM4-65NEGATIVE wnDirect Italy DO NOT USE
DM5-65NEGATIVE wnDirect Italy DO NOT USE
DMASOS-65NEGATIVE wnDirect Italy DO NOT USE
DM6-65NEGATIVE wnDirect Italy DO NOT USE
DMASOS1DHL
DMEBAY1DHL
This is what I want to get out
Stack RefID Name
DM1-102Australia Post
DM1-83NEGATIVE DPD Netherlands DO NOT USE
DM1-78NEGATIVE DPD Germany DO NOT USE
DM1-69NEGATIVE DPD Belgium DO NOT USE
DM1-65NEGATIVE wnDirect Italy DO NOT USE
DMASOS1DHL
SQL to generate the sample data
SELECT 'DM1','-102','Australia Post' UNION ALL
SELECT 'DM2','-102','Australia Post' UNION ALL
SELECT 'DM6','-102','Australia Post' UNION ALL
SELECT 'DM3','-102','Australia Post' UNION ALL
SELECT 'DM4','-102','Australia Post' UNION ALL
SELECT 'DM5','-102','Australia Post' UNION ALL
SELECT 'DMASOS','-102','StarTrack Express' UNION ALL
SELECT 'DM1','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM2','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM3','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM4','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM5','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM6','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL
SELECT 'DM1','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM2','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM3','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM4','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM5','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM6','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL
SELECT 'DM1','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM2','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM3','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM4','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM5','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DMMS','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM6','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL
SELECT 'DM1','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DM2','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DM3','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DM4','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DM5','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DMASOS','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DM6','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL
SELECT 'DMASOS','1','DHL' UNION ALL
SELECT 'DMEBAY','1','DHL'

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

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

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

What a great job posting consumable data!!! I turned this into a temp table to make this a little more clear (and use it myself). Here is how you can do this.

create table #Sample

(

Stack varchar(10),

RefID varchar(10),

Name varchar(100)

)

insert #Sample

SELECT 'DM1','-102','Australia Post' UNION ALL

SELECT 'DM2','-102','Australia Post' UNION ALL

SELECT 'DM6','-102','Australia Post' UNION ALL

SELECT 'DM3','-102','Australia Post' UNION ALL

SELECT 'DM4','-102','Australia Post' UNION ALL

SELECT 'DM5','-102','Australia Post' UNION ALL

SELECT 'DMASOS','-102','StarTrack Express' UNION ALL

SELECT 'DM1','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM2','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM3','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM4','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM5','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM6','-83','NEGATIVE DPD Netherlands DO NOT USE' UNION ALL

SELECT 'DM1','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM2','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM3','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM4','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM5','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM6','-78','NEGATIVE DPD Germany DO NOT USE' UNION ALL

SELECT 'DM1','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM2','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM3','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM4','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM5','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DMMS','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM6','-69','NEGATIVE DPD Belgium DO NOT USE' UNION ALL

SELECT 'DM1','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DM2','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DM3','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DM4','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DM5','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DMASOS','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DM6','-65','NEGATIVE wnDirect Italy DO NOT USE' UNION ALL

SELECT 'DMASOS','1','DHL' UNION ALL

SELECT 'DMEBAY','1','DHL';

with MyData as

(

select Stack

, RefID

, Name

, ROW_NUMBER() over (Partition by Stack order by RefID) as RowNum

from #Sample

)

select *

from MyData

where RowNum = 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/

vee_jess SSC Eights! Points: 984 More actions · Answer 2

Thanks for your reply but your query isn't giving me the correct result! But this one does...

with MyData as

(

select Stack

, RefID

, Name

, ROW_NUMBER() over (Partition by RefID order by Stack) as RowNum

from #Sample

)

select *

from MyData

where RowNum = 1

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

vee_jess (11/19/2014)
Thanks for your reply but your query isn't giving me the correct result! But this one does...
with MyData as
(
select Stack
, RefID
, Name
, ROW_NUMBER() over (Partition by RefID order by Stack) as RowNum
from #Sample
)
select *
from MyData
where RowNum = 1

Cool. Wasn't really sure which way that needed to be. Glad that got you a solution that would work. Even better that you obviously understand it since you were able to change it to work correctly. 🙂

_______________________________________________________________

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/

vee_jess SSC Eights! Points: 984 More actions · Answer 4

Window functions work wonders I heard and I am very keen on learning how to use this stuff. Any good literature on the web you would recommend?

Thanks for your help anyway!

pietlinden SSC Guru Points: 63428 More actions · Answer 5

pietlinden

SSC Guru

Points: 63428

November 19, 2014 at 8:34 am

#1759543

Itzik Ben-Gan's book on Window functions is good.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

pietlinden (11/19/2014)
Itzik Ben-Gan's book on Window functions is good.

+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/