CODEWITHSUNDEEP
sqloracle-database
sriram
sriram

Reputation: 378

select distinct not working as expected

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)";

Upvotes: 9

Views: 89486

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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

Upvotes: 3

Michael Berkowski
Michael Berkowski

Reputation: 270775

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

Upvotes: 9

Spags
Spags

Reputation: 541

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.

Upvotes: 1

Related Questions