order by problem - SQL Server Forums

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)

order by problem

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2008-03-06 : 09:16:57

Here is my query. I hope the query is self explanatory. I have a date table that contains the dates for the classes and a link table named ClassDate that links the classes in the Class table and the dates in the Date table. I want to display the classes in their date order. If I join the date table so I can use the order by statement then I get duplicate classes because of the many dates in the date table. I can use distinct to remove the duplicates. The problem is that the order by statement wants to then have the Date.StartDateTime in the select list. If I do that I get the duplicate classes again. I'm sure there is a simple answer to this question.

declare
@CityID tinyint,
@ProgramID tinyint,
@ClassTypeID tinyint,
@DateToHide smalldatetime

SELECT C.ClassID, C.NumSeatsInitial - C.NumSeatsFilled as AvailableSeats,
C.Comments, C.ClassAddress, C.FieldAddress, City.City, C.State, ClassType.ClassType, Program,

--Get the lead instructor for this class
(SELECT Instructor.FirstName + ' ' + Instructor.LastName
FROM Instructor
INNER JOIN ClassInstructor ON ClassInstructor.InstructorID = Instructor.InstructorID
WHERE ClassInstructor.ClassID = C.ClassID
AND ClassInstructor.IsLead = 1)AS LeadInstructor

FROM Class C
INNER JOIN City ON City.CityID = C.CityID
INNER JOIN Program ON Program.ProgramID = C.ProgramID
INNER JOIN ClassType ON ClassType.ClassTypeID = C.ClassTypeID
WHERE C.CityID = @CityID
AND Program.ProgramID = @ProgramID
AND ClassType.ClassTypeID = @ClassTypeID

--Remove from results based on DateToHide parameter
AND

(SELECT TOP 1 Date.StartDateTime
FROM Date
INNER JOIN ClassDate ON ClassDate.DateID = Date.DateID
WHERE ClassDate.ClassID = C.ClassID
ORDER BY StartDateTime) > @DateToHide

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-06 : 09:52:35

If class occurs for more than 1date. what date should you want to display against class, latest or first date?

forwheeler
Starting Member

44 Posts

Posted - 2008-03-06 : 10:02:11

quote:
Originally posted by visakh16

If class occurs for more than 1date. what date should you want to display against class, latest or first date?

I would like the classes ordered by the first class date.
Thanks

forwheeler
Starting Member

44 Posts

Posted - 2008-03-10 : 09:19:14

bump

quote:
Originally posted by forwheeler

quote:
Originally posted by visakh16

If class occurs for more than 1date. what date should you want to display against class, latest or first date?

I would like the classes ordered by the first class date.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 10:10:23

declare
@CityID tinyint,
@ProgramID tinyint,
@ClassTypeID tinyint,
@DateToHide smalldatetime

SELECT C.ClassID, C.NumSeatsInitial - C.NumSeatsFilled as AvailableSeats,
C.Comments, C.ClassAddress, C.FieldAddress, City.City, C.State, ClassType.ClassType, Program,

--Get the lead instructor for this class
(SELECT Instructor.FirstName + ' ' + Instructor.LastName
FROM Instructor
INNER JOIN ClassInstructor ON ClassInstructor.InstructorID = Instructor.InstructorID
WHERE ClassInstructor.ClassID = C.ClassID
AND ClassInstructor.IsLead = 1)AS LeadInstructor

FROM Class C
INNER JOIN (SELECT ClassID,MAX(StartDateTime) AS MaxDate
FROM ClassDate
GROUP BY ClassID)cdON cd.ClassID=c.ClassID
INNER JOIN City ON City.CityID = C.CityID
INNER JOIN Program ON Program.ProgramID = C.ProgramID
INNER JOIN ClassType ON ClassType.ClassTypeID = C.ClassTypeID
WHERE C.CityID = @CityID
AND Program.ProgramID = @ProgramID
AND ClassType.ClassTypeID = @ClassTypeID

--Remove from results based on DateToHide parameter
AND

(SELECT TOP 1 Date.StartDateTime
FROM Date
INNER JOIN ClassDate ON ClassDate.DateID = Date.DateID
WHERE ClassDate.ClassID = C.ClassID
ORDER BY StartDateTime) > @DateToHide
ORDER BY cd.MaxDate ASC

forwheeler
Starting Member

44 Posts

Posted - 2008-03-10 : 11:22:39

Thanks visakh16.
I changed the query a little since the StartDateTime field is in the Date table and not the ClassDate table and it works.
Here is the final query in case someone is doing something similar.

declare
@CityID tinyint,
@ProgramID tinyint,
@ClassTypeID tinyint,
@DateToHide smalldatetime

SELECT C.ClassID, C.NumSeatsInitial - C.NumSeatsFilled as AvailableSeats,
C.Comments, C.ClassAddress, C.FieldAddress, City.City, C.State, ClassType.ClassType, Program,

--Get the lead instructor for this class
(SELECT Instructor.FirstName + ' ' + Instructor.LastName
FROM Instructor
INNER JOIN ClassInstructor ON ClassInstructor.InstructorID = Instructor.InstructorID
WHERE ClassInstructor.ClassID = C.ClassID
AND ClassInstructor.IsLead = 1)AS LeadInstructor

FROM Class C
INNER JOIN(SELECT ClassID, MAX(StartDateTime)AS MaxDate
FROM [Date]
INNER JOIN ClassDate ON ClassDate.DateID = [Date].DateID
GROUP BY ClassDate.ClassID)CD
ON CD.ClassID = C.ClassID
INNER JOIN City ON City.CityID = C.CityID
INNER JOIN Program ON Program.ProgramID = C.ProgramID
INNER JOIN ClassType ON ClassType.ClassTypeID = C.ClassTypeID
WHERE C.CityID = @CityID
AND Program.ProgramID = @ProgramID
AND ClassType.ClassTypeID = @ClassTypeID

--Remove from results based on DateToHide parameter
AND

(SELECT TOP 1 Date.StartDateTime
FROM Date
INNER JOIN ClassDate ON ClassDate.DateID = Date.DateID
WHERE ClassDate.ClassID = C.ClassID
ORDER BY StartDateTime) > @DateToHide
ORDER BY CD.MaxDate ASC

quote:
Originally posted by visakh16

declare
@CityID tinyint,
@ProgramID tinyint,
@ClassTypeID tinyint,
@DateToHide smalldatetime

SELECT C.ClassID, C.NumSeatsInitial - C.NumSeatsFilled as AvailableSeats,
C.Comments, C.ClassAddress, C.FieldAddress, City.City, C.State, ClassType.ClassType, Program,

--Get the lead instructor for this class
(SELECT Instructor.FirstName + ' ' + Instructor.LastName
FROM Instructor
INNER JOIN ClassInstructor ON ClassInstructor.InstructorID = Instructor.InstructorID
WHERE ClassInstructor.ClassID = C.ClassID
AND ClassInstructor.IsLead = 1)AS LeadInstructor

FROM Class C
INNER JOIN (SELECT ClassID,MAX(StartDateTime) AS MaxDate
FROM ClassDate
GROUP BY ClassID)cdON cd.ClassID=c.ClassID
INNER JOIN City ON City.CityID = C.CityID
INNER JOIN Program ON Program.ProgramID = C.ProgramID
INNER JOIN ClassType ON ClassType.ClassTypeID = C.ClassTypeID
WHERE C.CityID = @CityID
AND Program.ProgramID = @ProgramID
AND ClassType.ClassTypeID = @ClassTypeID

--Remove from results based on DateToHide parameter
AND

(SELECT TOP 1 Date.StartDateTime
FROM Date
INNER JOIN ClassDate ON ClassDate.DateID = Date.DateID
WHERE ClassDate.ClassID = C.ClassID
ORDER BY StartDateTime) > @DateToHide
ORDER BY cd.MaxDate ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources