CODEWITHSUNDEEP
mysqlsqlselect
James Mitchell
James Mitchell

Reputation: 2467

Select only unique values from a table, not using count(*)

By unique, I mean if there is some value that has a duplicate, don't show either of them.

Example:

Student    |    College
-----------------------
Jake       |    Harvard
Josh       |    Penn State
Erica      |    Harvard

So in this case, the result would be

Penn State

the query would be something like, schools that only one student goes to.

I want to do this without using count(*).

Upvotes: 1

Views: 60

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

Assuming you have no strict duplicates, you can use not exists to see if there is another student in the college:

select t.college
from t
where not exists (select 1
                  from t t2
                  where t2.college = t.college and t2.student <> t.student
                 );

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

You can use LAG() and LEAD(), as in:

select
    *,
    lag(college) over(order by college) as prev_college
    lead(college) over(order by college) as next_college
  from my_table
  where college <> prev_college or prev_college is null
    and college <> next_college or next_college is null

Upvotes: 1

Shadow
Shadow

Reputation: 34285

You can use a self left join on the college field and different student and return only those records where there is no match:

select t1.college from yourtable t1
left join yourtable t2 on t1.collage=t2.college and t1.student<>t2.student
where t2.college is null

Upvotes: 3

Mureinik
Mureinik

Reputation: 311978

The restriction on not using count sounds a bit artificial, but assuming the combination of student and college is unique, you could compare the maximum and minimum per college and make sure they're the same student:

SELECT   college
FROM     mytable
GROUP BY college
HAVING   MIN(student) = MAX(student)

Upvotes: 3

Related Questions