SQL Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm selecting a DISTINCT column, but I want the rest of my columns to appear
in the result.

Right now, only my distinct column is appearing.

Thanks
 
To follow up, I know to list the rest of my columns separated by a comma. But
I can't do that in the select distinct line, because duplicates still appear
because in the other columns there is distinct data. I want to select the
distinct Student.ID's and after that, list the other fields.

Please let me know if I need to explain further.
 
What do you want to display if Student.ID exists multiple times, with
different values in the other fields?

If you use

SELECT DISTINCT ID, FIeld2, Field3
FROM Student

it'll return one row for each unique combination of ID, Field2, FIeld3 in
the table. However, if a specific value of ID does have different values for
Field2 and/or Field3, you'll get multiple rows for that ID.

You could try:

SELECT ID, Max(Field2), Max(Field3)
FROM Student
GROUP BY Field2, Field3
 
. I want to select the
distinct Student.ID's and after that, list the other fields.
Click to expand...

This is more than a one-line command. You probably need something like

select
s.studentID,

(select count(*) from courses
where courses.studentID = s.studentID
) as numberOfCourses,

(select max(StartDate) FROM Registrations
where registrations.studentID = s.studentID
) AS LatestRegistrationDate

from students s

where student.fullypaid = false



Hope that helps


Tim F
 
Back
Top