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)";
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.
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.
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.
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.
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
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With