Group BY question

Question

Group BY question

sql_novice_2007

Hall of Fame

Points: 3307
More actions
January 18, 2008 at 2:18 pm

#71343

Hi,
I have the following data and looikng to get the result of top 2 values of each depart ment and their names.
NamedeptCount
aaaccount12
bbaccount24
ccaccount22
ddIT23
eeIT67
ffIT665
ggIT6456
hhIT5
iiIT7
llaccount76
I want to select top 2 values from each department. How can I do this
I want the following result.
gg IT 6456
ff IT 665
ll account 76
bb account 24
Any help would be appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

Read up in your text book or Books On Line about the Top option and the GROUP BY function. You should be able to make it work from there. You should also look into subqueries.

If you need further help, post the code you have so far and we can take it from there.

Toni

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

This will do it... assuming that names are unique...

--===== Simulate the OPs table

-- This is NOT part of the solution

DECLARE @yourtable TABLE

(Name VARCHAR(10), Dept VARCHAR(10), Count INT)

INSERT INTO @yourtable

(Name, Dept, Count)

SELECT 'aa','account','12' UNION ALL

SELECT 'bb','account','24' UNION ALL

SELECT 'cc','account','22' UNION ALL

SELECT 'dd','IT','23' UNION ALL

SELECT 'ee','IT','67' UNION ALL

SELECT 'ff','IT','665' UNION ALL

SELECT 'gg','IT','6456' UNION ALL

SELECT 'hh','IT','5' UNION ALL

SELECT 'ii','IT','7' UNION ALL

SELECT 'll','account','76'

--===== Produce the desired output

SELECT t1.*

FROM @yourtable t1

WHERE t1.Name IN

(

SELECT TOP 2 --Change this number to vary # output

t2.Name

FROM @yourtable t2

WHERE t2.Dept = t1.Dept

ORDER BY t2.Count DESC

)

ORDER BY t1.Dept,

t1.Count DESC

I suspect that you didn't get a "code example answer" the first time because it's a bit of a pain to setup a test with the way you listed your example data. Please read the following for faster/better answers on future posts... thanks...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Shoot... I hope I didn't just help someone do their homework without them trying themselves, first...

--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)

toniupstny Hall of Fame Points: 3986 More actions · Answer 4

What's done is done. As you guessed, I did not post the code directly myself (instead providing areas to review that would allow creation of the solution) as it seemed a general question that would appear in homework.

Toni