unique top record
-
I am trying to retrieve the next class date for each location from my data.
This is what I have so far:
SELECT class.class_id, class.class_date, course.course_type_id, location.location_id, location.display_city, location.location_city
FROM class INNER JOIN
location ON class.location_id = location.location_id INNER JOIN
course ON class.course_id = course.course_id
WHERE (class.class_date > GETDATE()) AND (course.course_type_id = 3)
ORDER BY class.class_date
This the result that I am getting.
class_id class_date course_type_id location_id display_city location_city
1817 8/8/2007 3 22 Laguna Hills
1832 9/14/2007 3 31 San Jose
1833 10/5/2007 3 31 San Jose
1875 10/5/2007 3 33 Santa Rosa
1876 11/2/2007 3 33 Santa Rosa
1834 11/2/2007 3 31 San Jose
1835 12/7/2007 3 31 San Jose
1877 12/7/2007 3 33 Santa Rosa
1878 1/11/2008 3 33 Santa Rosa
1879 2/8/2008 3 33 Santa Rosa
1880 3/7/2008 3 33 Santa Rosa
What I would like the result to be is:
class_id class_date course_type_id location_id display_city location_city
1817 8/8/2007 3 22 Laguna Hills
1832 9/14/2007 3 31 San Jose
1875 10/5/2007 3 33 Santa Rosa
It is basically the next date for each location.
Thank you!
Norbert
-
Try this:
SELECT
dtclass.class_id,
dtclass.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM
(select
class.class_id,
class.class_date,
class.course_type_id,
class.location_id
from
dbo.class
where
class.class_id in (select top 1 sq.class_id from dbo.class sq where class.course_type_id = sq.course_type_id and class.location_id = sq.location_id order by sq.class_date asc)) dtclass
INNER JOIN location
ON dtclass.location_id = location.location_id
INNER JOIN course
ON dtclass.course_id = course.course_id
WHERE
(class.class_date > GETDATE()) AND (course.course_type_id = 3)
ORDER BY
dtclass.class_date
-
Hi Lynn,
Thank you for your help.
I cannot figure out why I am getting this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'class' does not match with a table name or alias name used in the query.
Thanks,
Norbert
-
DECLARE @Class TABLE (Class_ID int, Class_Date datetime, Location_ID int, Course_ID int)
DECLARE @Location TABLE (Location_ID int, Display_City varchar(50), Location_City varchar(50))
DECLARE @Course TABLE (Course_ID int, Course_Type_ID int)
SET NOCOUNT ON
INSERT INTO @Class
SELECT 1817, '2007-08-08', 22, 1 UNION ALL
SELECT 1832, '2007-09-14', 31, 1 UNION ALL
SELECT 1833, '2007-10-05', 31, 1 UNION ALL
SELECT 1875, '2007-10-05', 33, 1 UNION ALL
SELECT 1876, '2007-11-02', 33, 1 UNION ALL
SELECT 1834, '2007-11-02', 31, 1 UNION ALL
SELECT 1835, '2007-12-07', 31, 1 UNION ALL
SELECT 1877, '2007-12-07', 33, 1 UNION ALL
SELECT 1878, '2008-01-11', 33, 1 UNION ALL
SELECT 1879, '2008-02-08', 33, 1 UNION ALL
SELECT 1880, '2008-03-07', 33, 1
INSERT INTO @Location
SELECT 22, 'Laguna Hills', 'Laguna Hills' UNION ALL
SELECT 31, 'San Jose', 'San Jose' UNION ALL
SELECT 33, 'Santa Rosa', 'Santa Rosa'
INSERT INTO @Course
SELECT 1, 3
SELECT class.class_id,
class.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM @Class class
INNER JOIN @Location location
ON class.location_id = location.location_id
INNER JOIN @Course course
ON class.course_id = course.course_id
WHERE class.class_date > GETDATE() AND course.course_type_id = 3
ORDER BY class.class_date
SELECT class.class_id,
class.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM @Class class
INNER JOIN @Location location
ON class.location_id = location.location_id
INNER JOIN @Course course
ON class.course_id = course.course_id
INNER JOIN (
SELECT Location_ID, MIN(Class_Date) as Class_Date
FROM @Class
WHERE Class_Date > GETDATE()
GROUP BY Location_ID
) t
ON class.Class_Date = t.Class_Date AND class.Location_ID = t.Location_ID
WHERE class.class_date > GETDATE() AND course.course_type_id = 3
ORDER BY class.class_date
-
Check out John's code, I think it will do what you need also (I haven't tried myself). Also, as you didn't post the code you are running based on my post, I can't answer why you are getting an error. Did you copy my code completely, or did you just take part of it? I ran my code with out any problems before posting it.
-
Thank you Lynn. I copied your code exactly and I got the error.
I will test John's code next.
-
Just for me, can you post back what you copied? Thanks.
-
Hi Lynn,
Sorry for the delayed response.
I am getting this error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'class' does not match with a table name or alias name used in the query.
This is what I copied into Query Analyzer:
SELECT
dtclass.class_id,
dtclass.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM
(select
class.class_id,
class.class_date,
class.course_type_id,
class.location_id
from
dbo.class
where
class.class_id in (select top 1 sq.class_id from dbo.class sq where class.course_type_id = sq.course_type_id and class.location_id = sq.location_id order by sq.class_date asc)) dtclass
INNER JOIN location
ON dtclass.location_id = location.location_id
INNER JOIN course
ON dtclass.course_id = course.course_id
WHERE
(class.class_date > GETDATE()) AND (course.course_type_id = 3)
ORDER BY
dtclass.class_date
-
Here is the problem (only thing I can think of why it worked on my system is I am running on SQL Server 2005):
WHERE
(class.class_date > GETDATE()) AND (course.course_type_id = 3)
Should be:
WHERE
(dtclass.class_date > GETDATE()) AND (course.course_type_id = 3)
Give this change a try and let me know.
-
Hi Lynn,
The original error went away. Now I am getting these errors.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'course_type_id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'course_type_id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'course_type_id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'course_id'.
Thank you,
Norbert
-
Hi John - Thank you very much for your help. Sorry for the late response.
Your code works perfectly!
However, for some strange reason when I run your query against my real database, I am only getting 1 result back and not 2.
This is your 1st slightly modified query:
SELECT class.class_id,
class.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM class
INNER JOIN location
ON class.location_id = location.location_id
INNER JOIN course
ON class.course_id = course.course_id
WHERE class.class_date > GETDATE() AND course.course_type_id = 3
ORDER BY class.class_date
Returns:
class_id class_date course_type_id location_id display_city location_city
1832 2007-09-14 00:00:00.000 3 31 NULL San Jose
1833 2007-10-05 00:00:00.000 3 31 NULL San Jose
1875 2007-10-05 00:00:00.000 3 33 NULL Santa Rosa
1876 2007-11-02 00:00:00.000 3 33 NULL Santa Rosa
1834 2007-11-02 00:00:00.000 3 31 NULL San Jose
1835 2007-12-07 00:00:00.000 3 31 NULL San Jose
1877 2007-12-07 00:00:00.000 3 33 NULL Santa Rosa
1878 2008-01-11 00:00:00.000 3 33 NULL Santa Rosa
1879 2008-02-08 00:00:00.000 3 33 NULL Santa Rosa
1880 2008-03-07 00:00:00.000 3 33 NULL Santa Rosa
The following query which should return 2 results is only returning one.
SELECT class.class_id,
class.class_date,
course.course_type_id,
location.location_id,
location.display_city,
location.location_city
FROM class
INNER JOIN location
ON class.location_id = location.location_id
INNER JOIN course
ON class.course_id = course.course_id
INNER JOIN (
SELECT Location_ID, MIN(Class_Date) as Class_Date
FROM Class
WHERE Class_Date > GETDATE()
GROUP BY Location_ID
) t
ON class.Class_Date = t.Class_Date AND class.Location_ID = t.Location_ID
WHERE class.class_date > GETDATE() AND course.course_type_id = 3
ORDER BY class.class_date
Result of the query
class_id class_date course_type_id location_id display_city location_city
1875 2007-10-05 00:00:00.000 3 33 NULL Santa Rosa
It should also return:
class_id class_date course_type_id location_id display_city location_city
1832 2007-09-14 00:00:00.000 3 31 NULL San Jose
Cannot figure out why it's not pulling in the above row.
Thanks,
Norbert
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply