[MySQL] How to select only unique entires from table?

[Rikki]

Hi folks,

Probably the simplest question ever but one thats eluded answering for myself

I have a table with say 10 records and say that the records have a location field. How would I select only records with different locations ?

Eg:

1 - Jim - Florida
2 - Mark - Texas
3 - Kim - Florida
4 - Phil - Boston
5 - Gary - Los Angeles
6 - Warren - Texas
7 - Will - Florida
8 - Grant - Texas
9 - Lisa - New York
10 - Julie - New York

So my SELECT statement would produce :

1 - Jim - Florida
2 - Mark - Texas
4 - Phil - Boston
5 - Gary - Los Angeles
9 - Lisa - New York

I would want the full record returned not just the location.

Many thanks people, hope you can help

Regards,

Rikki

[jofa]

1st of all; why?
I mean why select Jim from FL, but not Kim?

Anyway, "group by location" maybe?

(And yes, grouping by location and not using an aggregate function for name will work in mysql, but not in other, normal dbs)

[compwizard]

I believe something like this would work:

SELECT DISTINCT location FROM tablename

or

SELECT location, count(location) FROM tablename GROUP BY location

would give you a count of each location

[compwizard]

Oops, on my first example, just add your other fields seperated by commas

[jofa]

Oops, on my first example, just add your other fields seperated by commas

Doesn't DISTINCT depend on all the selected fields?
I.e.
select distinct name, location ...
will return all 10 rows, while
select distinct location ...
will return only 5 rows

If there should be an 11th row with another Julie from NY, then distinct would eliminate one duplicate in the combination name+location

[r937]

yes, DISTINCT operates on all columns of the result set (i.e. all columns in the SELECT list)

all databases support GROUP BY without an aggregate in the SELECT list

"select only records with different locations" doesn't really make sense unless you specifiy how to choose which row you want amongst the multiple rows that have the same location

[compwizard]

Oops, my mistake...well anyway, my second example should work, even if it is not exactly what was asked for.

[jofa]

all databases support GROUP BY without an aggregate in the SELECT list

???

select location, name
from test
group by location

=> "Column 'test.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

While...
select location, max(name)
from test
group by location
... works OK
(not sure max(name) makes sense in any way )

DB = Sql Server 2000

[redemption]

I think what Rudy meant was that you don't need an aggregate in the SELECT clause for a GROUP BY to work.

You still need to GROUP BY any non-aggregate fields in the SELECT clause, which is why your example above didn't work.

[jofa]

Yep, what I meant with this "...grouping by location and not using an aggregate function for name...", was that a query like
select location, name
from XXX
group by location
shouldn't work normally, but in mysql it does (!)

And grouping by "location, name" wasn't an option, since it wouldn't return the requested result

[redemption]

Sorry I took it out of it's context by reading your last post. Still am not sure how that's related to your quoting Rudy though

[jofa]

I don't know either... I think I've had some sort of out of brain experience the whole day today

Main point is: you should always include columns in either an aggregate function or the group by
You can do non-standard things in mysql, and will most certainly get problems if/when moving to another type of db

[r937]

no, jofa, you're right, mysql does allow you to break the rule about mentioning GROUP BY columns in the SELECT list as non-aggregates... or vice versa, actually

bad, mysql, very bad

to be fair, they do put a warning on it, and say that it will produce unpredictable results

my other comment was simply that you do not need to have an aggregate in order to use a GROUP BY... but we all knew that, right?

[jofa]

"unpredictable results"

Now, that's something to be afraid of (compared to null )