Need help with select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with select statement

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-12-07 : 21:40:51

I have a table that has 4 columns in it. What I want to do is select the ClassSchedulingID's for the records that are grouped by classid and classgroup, that have the lowest slotcode. So, basically I am trying to do the following which of course does not work:

SELECT ClassSchedulingID
FROM ClassTable
GROUP BY ClassGroup, SlotCode
HAVING MIN (SlotCode).

What is the proper syntax to return what I am looking for?

With The data listed below I am expecting the following to be returned:
787
813
900

Thanks in advance.

CREATE TABLE [dbo].[ClassTable] (
[classschedulingid] [int] NOT NULL ,
[classid] [int] NOT NULL ,
[classgroup] [smallint] NOT NULL ,
[slotcode] [int] NOT NULL
)
GO

INSERT ClassTable VALUES (787, 228, 1, 1)
INSERT ClassTable VALUES (788, 228, 1, 2)
INSERT ClassTable VALUES (813, 222, 1, 1)
INSERT ClassTable VALUES (814, 222, 1, 2)
INSERT ClassTable VALUES (815, 222, 1, 3)
INSERT ClassTable VALUES (900, 230, 1, 8)
INSERT ClassTable VALUES (901, 230, 1, 9)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-07 : 22:29:00

[code]select c.*
from ClassTable c
inner join (select classid, classgroup, min(slotcode) as min_slotcode
from ClassTable
group by classid, classgroup) as m
on c.classid = m.classid
and c.classgroup = m.classgroup
and c.slotcode = m.min_slotcode[/code]

-----------------
[KH]

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-12-07 : 23:16:58

Thanks so much for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources