How do I select the first row from each subset of data within a query result?

Hi!

I've got a MS SQL 2000 query that produces the following result:

Schedule    Date         Class          Period               Count       
----------- ------------ -------------- -------------------- ----------- 
441         2008/07/28   DEVL 101       A3201                205 
441         2008/07/28   DEVL 101       ZR019                102
 ...
441         2008/07/28   DEVL 101       BF012                1
554         2009/02/14   DEVL 154       X5002                384 
554         2009/02/14   DEVL 154       W8098                98
 ...
554         2009/02/14   DEVL 154       H2309                5

Open in new window


From this query, I want to select the Schedule, Date, Class, and Period with the highest Count from the rows that contain the same values for Schedule, Date, and Class.

The result should look like this:

Schedule    Date         Class          Period               Count       
----------- ------------ -------------- -------------------- ----------- 
441         2008/07/28   DEVL 101       A3201                205 
554         2009/02/14   DEVL 154       X5002                384
 ...

Open in new window


LVL 2
PaulELSAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
SELECT t1.Schedule, t1.Date, t1.Class, t1.Period, t1.[Count]FROM SomeTable t1 INNER JOIN    (SELECT t2.Schedule, t2.Date, t2.Class, MAX(t2.[Count]) AS MaxCount    FROM SomeTable    GROUP BY t2.Schedule, t2.Date, t2.Class) x ON t1.Schedule = x.Schedule AND t1.Date = x.Date AND t1.Class = x.Class AND t1.[Count] = x.MaxCount
0
 
SkydiverFLCommented:
SELEC TOP 1 FROM...
0
 
jrb1Commented:
one way to do it (my preferred method)

select Schedule, Date, Class, Period, Count
from yourtable A
where count = (select max(count) from yourtable
                 where schedule = a.schedule
                 and date = a.date
                 and class = a.class
                and period = a.period)
0
 
PaulELSAuthor Commented:
Thanks, matthewspatrick!
0
 
Mohit VijayCommented:
In Below query, you need to change Table1 with your table name.

SELECT tbl.Schedule, tbl.Date, tbl.Class, tbl.Period, t1.[Count]
FROM (SELECT Schedule, Date, Class, MAX(Count) AS MaxCount
   FROM Table1
   GROUP BY Schedule, Date, Class) AS tbl2 LEFT OUTER JOIN Table1 tbl 
  ON tbl2.Schedule = tbl.Schedule AND tbl2.Date = tbl.Date AND tbl2.Class = tbl.Class AND tbl.Count= tbl2.MaxCount

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.