SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
-
May 28, 2003, 06:21 #1
- Join Date
- Jan 2002
- Location
- Scotland, UK
- Posts
- 530
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
[MySQL] How to select only unique entires from table?
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
-
May 28, 2003, 06:47 #2
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
-
May 28, 2003, 06:48 #3
- Join Date
- May 2002
- Location
- United States
- Posts
- 457
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I believe something like this would work:
Code:SELECT DISTINCT location FROM tablename
Code:SELECT location, count(location) FROM tablename GROUP BY location
Compwizard
"There are 10 kinds of people in this world -- those who know binary, and those who don't."
-
May 28, 2003, 06:52 #4
- Join Date
- May 2002
- Location
- United States
- Posts
- 457
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oops, on my first example, just add your other fields seperated by commas
Compwizard
"There are 10 kinds of people in this world -- those who know binary, and those who don't."
-
May 28, 2003, 07:32 #5
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by compwizard
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
-
May 28, 2003, 08:02 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 28, 2003, 08:45 #7
- Join Date
- May 2002
- Location
- United States
- Posts
- 457
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oops, my mistake...well anyway, my second example should work, even if it is not exactly what was asked for.
Compwizard
"There are 10 kinds of people in this world -- those who know binary, and those who don't."
-
May 28, 2003, 08:58 #8
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
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 2000Last edited by jofa; May 28, 2003 at 09:11.
-
May 28, 2003, 09:03 #9
- Join Date
- Sep 2001
- Location
- Singapore
- Posts
- 5,269
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 28, 2003, 09:09 #10
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 28, 2003, 09:13 #11
- Join Date
- Sep 2001
- Location
- Singapore
- Posts
- 5,269
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 28, 2003, 09:44 #12
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 28, 2003, 10:24 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
-
May 28, 2003, 10:32 #14
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
"unpredictable results"
Now, that's something to be afraid of (compared to null)
Bookmarks