Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

select distinct not working as expected

Tags:

sql

oracle

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)";
like image 573
sriram Avatar asked Mar 05 '12 18:03

sriram


People also ask

Why you shouldn't use SELECT distinct?

As a general rule, SELECT DISTINCT incurs a fair amount of overhead for the query. Hence, you should avoid it or use it sparingly. The idea of generating duplicate rows using JOIN just to remove them with SELECT DISTINCT is rather reminiscent of Sisyphus pushing a rock up a hill, only to have it roll back down again.

Does SELECT distinct * work?

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Does SELECT distinct affect performance?

Yes, the application needs to compare every record to the "distinct" records cache as it goes. You can improve performance by using an index, particularly on the numeric and date fields.

How do I use distinct in SELECT?

The distinct keyword is used with select keyword in conjunction. It is helpful when we avoid duplicate values present in the specific columns/tables. The unique values are fetched when we use the distinct keyword. SELECT DISTINCT returns only distinct (different) values.


3 Answers

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
like image 54
Michael Berkowski Avatar answered Oct 02 '22 04:10

Michael Berkowski


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
like image 31
Mosty Mostacho Avatar answered Oct 02 '22 02:10

Mosty Mostacho


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.

like image 41
Spags Avatar answered Oct 02 '22 04:10

Spags