Group BY question

Question

Post reply

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.

Name dept Count

aa account 12

bb account 24

cc account 22

dd IT 23

ee IT 67

ff IT 665

gg IT 6456

hh IT 5

ii IT 7

ll account 76

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

toniupstny

Hall of Fame

Points: 3986

January 18, 2008 at 4:16 pm

#769902

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: 1000539 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://qa.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)
Intro to Tally Tables and Functions

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

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