SQL Query

[t3chpr0digy]

Hello, I have a SQL database that I am trying to run and narrow it down quite a bit.

The database is for a calendar, and for a school district, on the main district homepage, I want to display events from all four high schools, but only the NEXT one in line.

Right now, my code is

Each school is given a unique two digit character, and the district is 00
East = 21
Central = 23
North = 22
West = 24

The only thing I'm missing is getting it so that only the next event will appear for each school so the homepage does not show 5 events for one school.

Hope this makes sense!

SELECT TOP 5 date,event,school,location,monthnm,datenm,colors
FROM calendar 
where date > GetDate()
AND school = '00'
ORDER BY date
";

[Perez_Bottoms]

The Top 5 expression returns 5 rows of data based on your where clause criteria. You have to add the min() aggregate function in the this case in order to limit the records based on school that fit the where clause criteria. For example see the Table Sample, Query Sample and Results below:

Table Sample:

Event, Date, School, Location
1, 01/01/2011, 21, East
2, 11/01/2011, 21, East
3, 03/01/2011, 21, East
4, 06/01/2011, 21, East
5, 08/01/2011, 21, East
6, 09/01/2011, 21, East
7, 01/01/2011, 22, West
8, 02/01/2011, 22, West
9, 12/01/2011, 22, West
10, 06/01/2011, 22, West
11, 08/01/2011, 22, West
12, 09/01/2011, 22, West
13, 01/01/2011, 23, Central
14, 02/01/2011, 23, Central
15, 11/01/2011, 23, Central
16, 10/01/2011, 23, Central
17, 08/01/2011, 23, Central
18, 09/01/2011, 23, Central
13, 01/01/2011, 24, North Central
14, 02/01/2011, 24, North Central
15, 11/10/2011, 24, North Central
16, 11/21/2011, 24, North Central
17, 12/01/2011, 24, North Central
18, 12/13/2011, 24, North Central
13, 01/01/2011, 25, South
14, 02/01/2011, 25, South
15, 11/01/2011, 25, South
16, 10/01/2011, 25, South
17, 10/11/2011, 25, South
18, 12/01/2011, 25, South
13, 01/01/2011, 26, North
14, 02/01/2011, 26, North
15, 11/01/2011, 26, North
16, 10/01/2011, 26, North
17, 12/01/2011, 26, North
18, 09/01/2011, 26, North

Query Sample:

SELECT Top 5 Min(EventDate) EventDate,School,Location
FROM #Calendar
WHERE EventDate > GetDate()
GROUP BY School,Location
ORDER BY EventDate

Results:

EventDate               School Location
----------------------- ------ ---------------
2011-08-01 00:00:00     21     East
2011-08-01 00:00:00     23     Central
2011-08-01 00:00:00     22     West
2011-09-01 00:00:00     26     North
2011-10-01 00:00:00     25     South

[Ezzaral]

Perhaps answering more recent threads would be more helpful? Pulling old threads like this back up to the top just causes confusion for everyone.

Closing.