select top 1 value per group

Question

select top 1 value per group

thbaig

Hall of Fame

Points: 3006
More actions
May 30, 2013 at 12:12 pm

#278006

Hi ,
table structure (tblA):
attribute varchar(20)
State varchar(10)
Sample data
attribute : State
5-5 : A
5-5 :AC
3-3 :A
3-3 :BC
4-4 :B
4-4 :C
I have only 3 types of attributes. I am using following sql to extract 1 value per group. I want to extract that with single sql without union All
Select top 1 Attribute,State from tblA where attribute ='5-5' order by 2
union all
Select top 1 Attribute,State from tblA where attribute ='3-3' order by 2
union all
Select top 1 Attribute,State from tblA where attribute ='4-4' order by 2

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

Below is one way to do this. Please take note of how I posted readily consumable sample data and ddl. This is something you should do when posting. Also, you should avoid reserved words for object or column names (state). Another suggestion is to not order by ordinal position, instead order by the column name. If your column order every changes it is really easy to miss the order by.

if OBJECT_ID('tempdb..#tblA') is not null

drop table #tblA

create table #tblA

(

Attribute varchar(20),

StateVal varchar(10)

)

insert #tblA

select '5-5', 'A' union all

select '5-5', 'AC' union all

select '3-3', 'A' union all

select '3-3', 'BC' union all

select '4-4', 'B' union all

select '4-4', 'C'

;with MyCTE as

(

select Attribute, StateVal, ROW_NUMBER() over(partition by Attribute order by StateVal) as RowNum

from #tblA

)

select Attribute, StateVal

from MyCTE

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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Simple aggregate will do the same:

select Attribute, MIN(StateVal) AS StateVal

from #tblA

group by Attribute

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thbaig Hall of Fame Points: 3006 More actions · Answer 3

thbaig

Hall of Fame

Points: 3006

May 31, 2013 at 6:24 am

#1620035

Thank you Sean Lange, it worked 🙂

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

Eugene Elutin (5/31/2013)
Simple aggregate will do the same:
select Attribute, MIN(StateVal) AS StateVal
from #tblA
group by Attribute

/facepalm

I guess I got hung up on the OP using the order by and went the hard way. :blush:

Eugene's solution is far simpler than mine.

_______________________________________________________________

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/

thbaig Hall of Fame Points: 3006 More actions · Answer 5

yes, but I have to add some other conditions and they worked with your logic 🙂

I would thank you both for valuable suggestions

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

thbaig1 (5/31/2013)
yes, but I have to add some other conditions and they worked with your logic 🙂
I would thank you both for valuable suggestions

If it's not a secret, what kind of other conditions you have added?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thbaig Hall of Fame Points: 3006 More actions · Answer 7

not a secrete 🙂

I have product related data in database. one of the ex.dev wrote query to fetch results in 3 with top1 as I mentioned. Now I have got another tweak to add for pricing based on condition. I have achieved it with grouping but I knew it can be simpler code.

So I can't use MIN , because I have to compute some other business logic. like if our price is lesser then partner than show us and viceversa. also on same price we have to show our product.I am sorry that I can't communicate exact details 🙁

So with over partition and order by conclude what I needed.

Schema is not normalized and include several tables to extract data. I will try to simulate it simple and post.

thanks again