SQL Challenge

[Argo]

Ok, I've been thinking about this one for a while, but couldn't come up with anything good.

I have a table that has 2 fields (for simplicity sake) 'user' and 'session'. Neither of these fields is the primary key, both have duplicates. Two different users cannot have the same session id, but the same user can have multiple instances with the same session id. The goal is to find all groups of (user, session) where session is unique. In other words, in a table:

Richard 1022
Richard 1022
Sam 1033
Richard 1022
Karen 1120
Karen 1122
Karen 1120


The result should be:

Richard 1022
Sam 1033
Karen 1120
Karen 1122

 

[IJump]

This does it when I tested it in Access 2000


SELECT user, session from table1 group by user, session

 

[Descartes]

SELECT user, session from table1 group by user, session

That will work in this particular case, but it's not the best approach to retrieve distinct records within a resultset as you're not really wanting to group anything, you just want the distinct records. Try...

select distinct user, session from table

 

[IJump]

Originally posted by: Descartes

SELECT user, session from table1 group by user, session

That will work in this particular case, but it's not the best approach to retrieve distinct records within a resultset as you're not really wanting to group anything, you just want the distinct records. Try...

select distinct user, session from table

distinct!! that was the word I was looking for.... I kept thinking unique and knew that wasn't it....

 

[joinT]

hehe, gotta love distinct~!

 

[payjo]

distinct is definitely the way to go with this problem