SQL query - 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

SQL Server Development (2000)

SQL query

Author

Topic

syraq
Starting Member

7 Posts

Posted - 2005-05-27 : 09:28:39

Hello

I hope someone can help me with this little problem.

Let us take two tables, student and course.
Pseudo code:

CREATE TABLE student (
uid varchar (10),
period varchar (10),
name varchar(50),
date datetime
)
PRIMARY KEY(uid, period)

CREATE TABLE course (
course_id varchar (10),
uid varchar (10)
)
PRIMARY KEY(course_id)

Table student contains some personal data and table course which course the student is taking.

Example:
001 1 Brad 2004-04-04
000 2 Bradh 2005-05-05
002 1 Tom 2004-10-10
002 2 Thomas 2005-03-03
003 1 Susan 2005-05-05

Observe that the same student name may be spellt differently. I have no control over this.

course migth look something like this:
100 001
100 002
101 003

How do I search for all students in one course and only return one row for each student, example:

SELECT student.uid, student.name ... WHERE course.course_id = '100'

RESULT:
001 Bradh
002 Thomas

The resulting rows should contain the last entry according to the date column.

/Roger

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-27 : 09:40:09

[code]SELECT student.uid, student.name
From student
WHERE course.course_id = '100' and exists
(Select 1
From student st
Where st.uid = student.uid
Group by st.date
Having st.date = max(st.date))[/code]

A candle loses nothing by lighting another candle

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-27 : 09:46:24

quote:

Originally posted by tuenty

SELECT student.uid, student.name 
From student
WHERE course.course_id = '100' and exists
    (Select 1 
     From student st
     Where st.uid = student.uid
     Group by st.date
     Having st.date = max(st.date))

Would this work?

SELECT S.uid, name, course_id, date
  From student S
  INNER JOIN course C
    ON  C.uid = S.uid
  WHERE course_id = '100'
  GROUP BY S.uid, name, course_id, date
  HAVING date = MAX(date)
  ORDER BY course_id, date, name

Drop the WHERE condition and it'll list 'em for all courses in course_id order.

syraq
Starting Member

7 Posts

Posted - 2005-05-27 : 09:53:54

quote:

Originally posted by tuenty

SELECT student.uid, student.name 
From student
WHERE course.course_id = '100' and exists
    (Select 1 
     From student st
     Where st.uid = student.uid
     Group by st.date
     Having st.date = max(st.date))

A candle loses nothing by lighting another candle

This query gives all rows for each student, I only want one row.
I can not group by date since that is not the same for one student.

/Roger

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-27 : 09:57:15

quote:
Originally posted by syraq

I can not group by date since that is not the same for one student.

I'm sure you have identified a problem, but this doesn't make sense. I GROUP BY fields all the time that are not the same for one field (in this case - student).

syraq
Starting Member

7 Posts

Posted - 2005-05-27 : 10:06:56

quote:
Originally posted by SamC

quote:
Originally posted by syraq

I can not group by date since that is not the same for one student.

I'm sure you have identified a problem, but this doesn't make sense. I GROUP BY fields all the time that are not the same for one field (in this case - student).

Ok, I probably frased myself poorly. What I meant was that the query does not return one row for each student. If I'm not misstaken, that is because of the GROUP BY clause.

/Roger

syraq
Starting Member

7 Posts

Posted - 2005-05-27 : 10:13:53

quote:
Originally posted by SamC

[quote]
Would this work?
SELECT S.uid, name, course_id, date
From student S
INNER JOIN course C
ON C.uid = S.uid
WHERE course_id = '100'
GROUP BY S.uid, name, course_id, date
HAVING date = MAX(date)
ORDER BY course_id, date, name

Drop the WHERE condition and it'll list 'em for all courses in course_id order.

Thank you for giving input on this. It seems to be a simple problem but I am getting nowhere with it.
I tried the query above but it doesn't work. I am not really sure what it returns but it is not correct.

/Roger

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-27 : 11:37:33

quote:
Originally posted by syraq

I am not really sure what it returns but it is not correct.

quote:
How do I search for all students in one course and only return one row for each student, example:

SELECT student.uid, student.name ... WHERE course.course_id = '100'

RESULT:
001 Bradh
002 Thomas

Well, you could remove the last two fields in the select I proposed to get the result above.

SELECT S.uid, name
  From student S
  INNER JOIN course C
    ON  C.uid = S.uid
  WHERE course_id = '100'
  GROUP BY S.uid, name, course_id, date
  HAVING date = MAX(date)
  ORDER BY course_id, date, name

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-27 : 12:32:09

quote:

Originally posted by tuenty

SELECT student.uid, student.name 
From student inner join Course on student.uid= course.uid
WHERE course.course_id = '100' and exists
    (Select 1 
     From student st
     Where st.uid = student.uid
     Group by st.date st.uid
     Having st.date = max(st.date))

A candle loses nothing by lighting another candle

Sorry

the group by should've be by the uid not the date and of course I left out the inner join with the course table. I haven't try it but it should work now

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-27 : 12:51:52

quote:

Originally posted by SamC

[

SELECT S.uid, name
  From student S
  INNER JOIN course C
    ON  C.uid = S.uid
  WHERE course_id = '100'
  GROUP BY S.uid, name, course_id, date *
  HAVING date = MAX(date)**
  ORDER BY course_id, date, name

*If you group by name then you will have more than one row per student if the student name has been misspelled

**HAVING date = MAX(date) will reduce your selection to only those students who's date is the same as the max(date) of the table instead of selecting the row with the greater date for each student (for each uid)

A candle loses nothing by lighting another candle

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-27 : 13:54:52

quote:
Originally posted by tuenty

*If you group by name then you will have more than one row per student if the student name has been misspelled

**HAVING date = MAX(date) will reduce your selection to only those students who's date is the same as the max(date) of the table instead of selecting the row with the greater date for each student (for each uid)

Point 1 is true, but I suggest we drop this point as a refinement to be handled later.

Point 2 is incorrect. MAX(date) is unique for each uid and name pair. It is not MAX for the entire table.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-27 : 15:05:15

ok!

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-27 : 15:06:51

here is the answer

Declare  @student TABLE (
uid varchar (10),
period varchar (10),
name varchar(50),
[date] datetime
)

Declare @course TABLE  (
course_id varchar (10),
uid varchar (10)
)

Insert into @student
Select '001', 1, 'Brad', '2004-04-04' Union
Select '000', 2, 'Bradh', '2005-05-05' Union
Select '002', 1, 'Tom', '2004-10-10' Union
Select '002', 2, 'Thomas', '2005-03-03' Union
Select '003', 1, 'Susan', '2005-05-05' 

Insert into @course
Select '100', '001' Union
Select '100', '002' Union
Select '101', '003'

SELECT student.uid, student.name 
From @student student inner join @Course course on student.uid= course.uid
WHERE course.course_id = '100' and student.date =
    (Select max(st.date) 
     From @student st
     Where st.uid = student.uid
     Group by st.uid)

Results

uid        name                                               
---------- -------------
001        Brad
002        Thomas

A candle loses nothing by lighting another candle

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-27 : 18:48:45

Looks right. I didn't notice that student.uid was a unique ID for each student... solves the spelling issue mentioned.

r937
Posting Yak Master

112 Posts

Posted - 2005-05-28 : 10:19:28

quote:

Originally posted by tuenty

WHERE ... student.date =
    (Select max(st.date) 
     From @student st
     Where st.uid = student.uid
     Group by st.uid)

the GROUP BY is not required in a correlated subquery, because there is only one group

rudy
http://r937.com/

syraq
Starting Member

7 Posts

Posted - 2005-05-30 : 02:53:56

quote:

Originally posted by tuenty

here is the answer

Declare  @student TABLE (
uid varchar (10),
period varchar (10),
name varchar(50),
[date] datetime
)

Declare @course TABLE  (
course_id varchar (10),
uid varchar (10)
)

Insert into @student
Select '001', 1, 'Brad', '2004-04-04' Union
Select '000', 2, 'Bradh', '2005-05-05' Union
Select '002', 1, 'Tom', '2004-10-10' Union
Select '002', 2, 'Thomas', '2005-03-03' Union
Select '003', 1, 'Susan', '2005-05-05' 

Insert into @course
Select '100', '001' Union
Select '100', '002' Union
Select '101', '003'

SELECT student.uid, student.name 
From @student student inner join @Course course on student.uid= course.uid
WHERE course.course_id = '100' and student.date =
    (Select max(st.date) 
     From @student st
     Where st.uid = student.uid
     Group by st.uid)

Results

uid        name                                               
---------- -------------
001        Brad
002        Thomas

A candle loses nothing by lighting another candle

This code works! Thank you very much to you and all the others that contributed to this problem.

I'll buy the next round of beer

/Roger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources