SQL DISTINCT for one field whilst still selecting others

Question

Looking for a way to only select rows with unique email addresses but also I need the other field data.

Currently have this:

$mainQuery = $mysqli->query("SELECT DISTINCT(`email`) FROM valuations");

Which brings my results down from 9208 to 7848 so that works fine.

I have 3 or 4 tables with 10000 or so rows each and we are now looking to create one single 'customers' table. I am making a script to 'import' all of these rows into the customers table.

The rows I need extra are e.g. 'username' 'number' 'title' etc.

These do not need to be unique, just the email.

I tried:

$mainQuery = $mysqli->query("SELECT DISTINCT(`email`),username FROM valuations");

But that returned more rows that before, how should I go about this?

Thanks.

DISTINCT is not a function. In the event that there are multiple usernames associated with one email, which one do you want returned. Note that the typical newbie response of "it doesn't matter" is often indicative of poor design. It SHOULD matter! — Strawberry, Jan 29 '15 at 11:35
Saw it in an example somewhere... changed it to just DISTINCT email. — Lovelock, Jan 29 '15 at 11:37
Are you looking for something like `GROUP BY`? `SELECT * FROM valuations GROUP BY email` — Anthony, Jan 29 '15 at 11:38
Oh, and when you say "10,000 fields", you mean "10,000 rows", right?!?! — Strawberry, Jan 29 '15 at 11:38
yes rows :-) @Strawberry group by would make more sense, ill have a play! — Lovelock, Jan 29 '15 at 11:39
@Anthony I edited the question to say rows, I know editing a question with answers is frowned upon but I wanted to remove the chance for others to correct me :o) — Lovelock, Jan 29 '15 at 11:49
@Strawberry - relax, the OP will figure out soon enough why this isn't what they really want. — Anthony, Jan 29 '15 at 11:52
Is editing a question with answers frowned upon? I didn't know. — Strawberry, Jan 29 '15 at 12:04
[Good/fuller answer](http://stackoverflow.com/questions/11641270/mysql-select-all-columns-where-one-column-is-distinct) on this stackoverflow question :) — devsie, Mar 05 '16 at 19:38

score 0 · Answer 1 · answered Jan 29 '15 at 11:42

0

Use group by:

SELECT email, MIN(username)
FROM valuations
GROUP BY email;

answered Jan 29 '15 at 11:42

Gordon Linoff

1,242,037
58
646
786

SQL DISTINCT for one field whilst still selecting others

1 Answers1