select distinct not working as expected

Question

Student group table:

STDNT_GROUP

studentId,CollegeID,studentgroup,flag
1,007,panthers,y
2,003,owls,n
3,007,owls,y
1,007,texans,y
2,003,texans,n

Expected output

1,007
2,003
3,007

I want unique student ids as well as their respective college ids.

I tried this query:

select distinct(studentId),collegeID from;

and

select studentId,collegeID from STDNT_GROUP where studentId in(select distinct(studentId) from STDNT_GROUP)";

Answer 1

Using DISTINCT without parentheses should get you what you want. DISTINCT should be thought of as a clause, rather than a function to which you pass a column name as an argument. It returns the set of distinct rows over the superset returned by the query, rather than distinct values in a single column.

SELECT DISTINCT
  studentId,
  CollegeID
FROM STUDENT_GROUP

Answer 2

You could use this for unsorted results:

select distinct studentid, collegeid from stdnt_group

Or this:

select studentid, collegeid from stdnt_group
group by studentid, collegeid

Or you could add an order by clause to any of them to retrieve them in order:

[previous query]
order by studentid, collegeid

Answer 3

The issue may be your use of paranthesis in the distinct call. Try just:

      SELECT DISTINCT studentID, collegeID

It may be helpful to provide what output you are actually getting as opposed to just the expected output. That may help us better determine whatmay be going wrong.