SELECT DISTINCT then additional fields

[rossbruniges]

heya guys,

I am trying to do the equivalent of :

Code:

SELECT DISTINCT email, * FROM student_bookings

I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email.

I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that )

Hope someone can help,

Thanks very very much!

[guelphdad]

If there are multiple emails then which one of those do you want to keep? It would affect the other data you would retrieve right?

say you have:

email firstname lastname lastlogin
dlake@home.com Dave Lake 2006-04-21
dlake@home.com Dave Lake 2006-04-20

you will need to decide if the lastlogin needs to be the latest or earliest or if it doesn't matter. also the same thing can crop up in other columns.

show us the other column names you want to retrieve and let us know if there are any restrictions on the data (like the lastlogin) above that you want to retrieve.

also, in case you didn't know, DISTINCT always works on the entire row of data. Some people try to use it as a function something like:

select distinct(email), firstname, lastname, lastlogin

which it can't do.

[rossbruniges]

we do hold the date when they registered so essentually we want to select their earliest one - thats all cool.

I realise your point about DISTINCT working on rows so thats why I posted this question - ultimatly I would like to use a subquery along the lines of

SELECT first_name, surname, register_Date FROM (SELECT DISTINCT email FROM student_bookings)

but as we aren't using a version of mySQL which supports subqueries I am trying to find if its possible to do it otherways.

Hope this isn't the case but if it is it is :( So is it????

Thanks for the help,

[guelphdad]

Okay that extra information makes the query easier. By the way, you never need to use subqueries, all subqueries can be rewritten as a join.

Do you have a primary key in the table (an auto_increment column perhaps)? If you don't then you should add one to differentiate one row from the other.
If you have a student number that would even be easy enough. I'll just add an id column here for demonstration sake, you can add it if you need it.

What you need here is a self join on the table:


SELECT
sb1.id,
sb1.first_name,
sb1.surname,
sb1.register_Date,
sb1.email
from student_bookings as sb1
inner join student_bookings as sb2
on sb1.id = sb2.id
group by
sb1.id,
sb1.first_name,
sb1.last_name,
sb1.register_Date,
sb1.email
having sb1.register_Date = min(sb2.register_Date)


Now that query grabs each student with their minimum registration date. The thing it won't do is return a single row if two students shared an email address, but I would assume that they wouldn't. But at least for each of the two students say, they would still only have a single row returned in the query.