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:
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:
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 5From 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
...
SkydiverFL
SELEC TOP 1 FROM...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
Thanks, matthewspatrick!
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