 | |
#1
 
| |||
| |||
|
Select Distinct Help -
11-21-2010
, 01:35 PM
#2
| |||
| |||
|
|
I want to return the primary key (keyID) and a text field(name) where the text field is Distinct. *This would look like: SELECT keyID, DISTINCT name FROM mytable.. *This doesn't work, so I tried SELECT keyID, name FROM mytable WHERE name=(SELECT DISTINCT name FROM mytable). Again, no joy!! I've run out of ideas. *So, suggestions please?? Thanks... Bruce |
was like this:
keyID name
1 Fred
2 Harry
3 Fred
4 Jim
5 Fred
6 Harry
We know that you want 3 records (one each of Fred, Harry, Jim), but
how are you choosing which keyID you wish to be returned for each of
the non-unique names?
#3
| |||
| |||
|
|
On Nov 21, 7:35*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote: I want to return the primary key (keyID) and a text field(name) where the text field is Distinct. *This would look like: SELECT keyID, DISTINCT name FROM mytable.. *This doesn't work, so I tried SELECT keyID, name FROM mytable WHERE name=(SELECT DISTINCT name FROM mytable). Again, no joy!! I've run out of ideas. *So, suggestions please?? Thanks... Bruce You need to tell us one more piece of information. Suppose your table was like this: keyID name 1 * * * Fred 2 * * * Harry 3 * * * Fred 4 * * * Jim 5 * * * Fred 6 * * * Harry We know that you want 3 records (one each of Fred, Harry, Jim), but how are you choosing which keyID you wish to be returned for each of the non-unique names? |
Fred, Harry, Jim found,
Assuming the FIRST one found is the one that is kept as the DISTINCT
one, my result rows would be
{1,Fred}, {2, Harry} and {4,Jim}.
Okay?
Bruce
#4
| |||
| |||
|
|
On Nov 21, 2:46*pm, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote: On Nov 21, 7:35*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote: I want to return the primary key (keyID) and a text field(name) where the text field is Distinct. *This would look like: SELECT keyID, DISTINCT name FROM mytable.. *This doesn't work, so I tried SELECT keyID, name FROM mytable WHERE name=(SELECT DISTINCT name FROM mytable). Again, no joy!! I've run out of ideas. *So, suggestions please?? Thanks... Bruce You need to tell us one more piece of information. Suppose your table was like this: keyID name 1 * * * Fred 2 * * * Harry 3 * * * Fred 4 * * * Jim 5 * * * Fred 6 * * * Harry We know that you want 3 records (one each of Fred, Harry, Jim), but how are you choosing which keyID you wish to be returned for each of the non-unique names? Thanks for the response. *The keyID I want is the one for the DISTINCT Fred, Harry, Jim found, Assuming the FIRST one found is the one that is kept as the DISTINCT one, my result rows would be {1,Fred}, {2, Harry} and {4,Jim}. Okay? Bruce |
first one or do you want the last one or do you want some other one.
If you want the first or the last one, then the answer is the
ubiquitous "Strawberry Query" which you will see many times in this
group.
#5
| |||
| |||
|
|
If you want the first or the last one, then the answer is the ubiquitous "Strawberry Query" which you will see many times in this group. |
Why not something like:
select name, max(keyID) as MaxKey
from mytable
group by name
order by name
--
Erick
#6
| |||
| |||
|
|
On Nov 21, 8:00*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote: On Nov 21, 2:46*pm, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote: On Nov 21, 7:35*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote: I want to return the primary key (keyID) and a text field(name) where the text field is Distinct. *This would look like: SELECT keyID, DISTINCT name FROM mytable.. *This doesn't work, so I tried SELECT keyID, name FROM mytable WHERE name=(SELECT DISTINCT name FROM mytable). Again, no joy!! I've run out of ideas. *So, suggestions please?? Thanks... Bruce You need to tell us one more piece of information. Suppose your table was like this: keyID name 1 * * * Fred 2 * * * Harry 3 * * * Fred 4 * * * Jim 5 * * * Fred 6 * * * Harry We know that you want 3 records (one each of Fred, Harry, Jim), but how are you choosing which keyID you wish to be returned for each of the non-unique names? Thanks for the response. *The keyID I want is the one for the DISTINCT Fred, Harry, Jim found, Assuming the FIRST one found is the one that is kept as the DISTINCT one, my result rows would be {1,Fred}, {2, Harry} and {4,Jim}. Okay? Bruce My question was "Which one do you want to be found?". Do you want the first one or do you want the last one or do you want some other one. If you want the first or the last one, then the answer is the ubiquitous "Strawberry Query" which you will see many times in this group. |
Not sure what the "Strawberry Query" is but I'll search the discussion
group.
Thanks...
#7
| |||
| |||
|
|
Captain Paralytic: If you want the first or the last one, then the answer is the ubiquitous "Strawberry Query" which you will see many times in this group. Is a strawberry query really necessary, here? Why not something like: select name, max(keyID) as MaxKey from mytable group by name order by name -- Erick |
Apart from the fact that I would write this as
SELECT
name,
min( keyID ) keyID
FROM mytable
GROUP BY name
ORDER BY name
In order to (a) keep it tidy and (b) give his suggested results, I
would still go for the Strawberry Query in order to not hit troubles
when another column is added to the query (as it inevitably is).
The Strawberry Query version would be:
SELECT
a.keyID,
a.name
FROM mytable a
LEFT JOIN mytable b ON a.name = b.name AND a.keyID > b.keyID
WHERE b.name IS NULL
#8
| |||
| |||
|
|
Captain Paralytic: If you want the first or the last one, then the answer is the ubiquitous "Strawberry Query" which you will see many times in this group. Is a strawberry query really necessary, here? Why not something like: select name, max(keyID) as MaxKey from mytable group by name order by name -- Erick |
what you are doing. I'm novice enough to have never done a select like
this..
Thanks...
#9
| |||
| |||
|
|
On Nov 21, 8:10*pm, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m wrote: Captain Paralytic: If you want the first or the last one, then the answer is the ubiquitous "Strawberry Query" which you will see many times in this group. Is a strawberry query really necessary, here? Why not something like: select name, max(keyID) as MaxKey from mytable group by name order by name -- Erick Well... Apart from the fact that I would write this as SELECT *name, *min( keyID ) keyID FROM mytable GROUP BY name ORDER BY name In order to (a) keep it tidy and (b) give his suggested results, I would still go for the Strawberry Query in order to not hit troubles when another column is added to the query (as it inevitably is). The Strawberry Query version would be: SELECT * a.keyID, * a.name FROM mytable a LEFT JOIN mytable b ON a.name = b.name AND a.keyID > b.keyID WHERE b.name IS NULL |
group the output by name, then order each name within the group. Once
that is done, the SELECT will retrieve the record for each name having
the Maxid (From Erick) or the Minid(from yours) This gives me what I
want.
Thanks....
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.