> This does give me each different name but now I need the ID.
> So I tried:
> SELECT DISTINCT name, id FROM table;
> But then I pulled all names and values as I was suspecting the DISTINCT
> is encompassing both name and id.
> Is this even possible on a single query or will I need to use 2 queries
> which really is not a problem but would like to learn a single query
> approach.
> Thanks
> Scotty
OK to answer my own question I found this.
SELECT name, MIN(id) FROM table GROUP BY name;
I would be open to any other way or is this the proper way (if there is such a thing)
> This does give me each different name but now I need the ID.
Well, in your setup, there is no such thing as THE id, you'll need some criterium to discern between these.
I guess this is not the full extent of the data in your table, but you are clearly missing a real identifying column (domain in ERD speech).
Herman Viaene
> So I tried:
> SELECT DISTINCT name, id FROM table;
> But then I pulled all names and values as I was suspecting the DISTINCT
> is encompassing both name and id.
> Is this even possible on a single query or will I need to use 2 queries
> which really is not a problem but would like to learn a single query
> approach.
> Thanks
> Scotty
-- Veel mensen danken hun goed geweten aan hun slecht geheugen. (G. Bomans)
Lots of people owe their good conscience to their bad memory (G. Bomans)
On Mon, 15 Oct 2012 10:13:27 +0200, herman.viaene wrote:
>> This does give me each different name but now I need the ID.
> Well, in your setup, there is no such thing as THE id, you'll need some
> criterium to discern between these.
> I guess this is not the full extent of the data in your table, but you
> are clearly missing a real identifying column (domain in ERD speech).
Not necessarily, it might just be that he wants any class / task / purchase "Id" that the student / employee / customer "name1" is associated with.
I think there are four functions that the OP can use, min and max (as he has already discovered), elt and coalesce, I'd suggest using 1 as the first arg to the former:
select nom as name, elt(1,id) as id from nomid group by name;
select nom as name, coalesce(id) as id from nomid group by name;
> On Mon, 15 Oct 2012 10:13:27 +0200, herman.viaene wrote:
>>> This does give me each different name but now I need the ID.
>> Well, in your setup, there is no such thing as THE id, you'll need some
>> criterium to discern between these.
>> I guess this is not the full extent of the data in your table, but you
>> are clearly missing a real identifying column (domain in ERD speech).
> Not necessarily, it might just be that he wants any class / task /
> purchase "Id" that the student / employee / customer "name1" is
> associated with.
> I think there are four functions that the OP can use, min and max (as he
> has already discovered), elt and coalesce, I'd suggest using 1 as the
> first arg to the former:
> select nom as name, elt(1,id) as id from nomid group by name;
> select nom as name, coalesce(id) as id from nomid group by name;
> Rgds
> Denis McMahon
Thanks Denis I have not read on elt or coalesce, but will do so now.
>> I only need one of the ID's per group of names.
>> I tried DISTINCT
>> SELECT DISTINCT name FROM table;
>> This does give me each different name but now I need the ID.
>> So I tried:
>> SELECT DISTINCT name, id FROM table;
>> But then I pulled all names and values as I was suspecting the DISTINCT
>> is encompassing both name and id.
>> Is this even possible on a single query or will I need to use 2 queries
>> which really is not a problem but would like to learn a single query
>> approach.
>> Thanks
>> Scotty
> OK to answer my own question I found this.
> SELECT name, MIN(id) FROM table GROUP BY name;
> I would be open to any other way or is this the proper way (if there is > such a thing)
If you only want one record and min(id) does what you want, then run with it. I doubt that you'll find a more efficient way of doing it.
> "Scott Johnson" <nooneh...@chalupasworld.com> wrote in message
> news:k5fp3s$9gv$1@dont-email.me...
>> On 10/14/2012 6:24 PM, Scott Johnson wrote:
>>> I have a table such as:
>>> I only need one of the ID's per group of names.
>>> I tried DISTINCT
>>> SELECT DISTINCT name FROM table;
>>> This does give me each different name but now I need the ID.
>>> So I tried:
>>> SELECT DISTINCT name, id FROM table;
>>> But then I pulled all names and values as I was suspecting the DISTINCT
>>> is encompassing both name and id.
>>> Is this even possible on a single query or will I need to use 2 queries
>>> which really is not a problem but would like to learn a single query
>>> approach.
>>> Thanks
>>> Scotty
>> OK to answer my own question I found this.
>> SELECT name, MIN(id) FROM table GROUP BY name;
>> I would be open to any other way or is this the proper way (if there
>> is such a thing)
> If you only want one record and min(id) does what you want, then run
> with it. I doubt that you'll find a more efficient way of doing it.
On Mon, 15 Oct 2012 15:23:25 +0200, Lennart Jonsson wrote:
> On 10/15/2012 02:03 PM, Denis McMahon wrote: [...]
>> select nom as name, coalesce(id) as id from nomid group by name;
> Whats the purpose of coalesce (in this context)?
coalesce(id) and elt(1,id) will both return the first id that appears in the grouped list of ids for the name.
This might not always be the same as min(id), but coalesce(id) and elt
(1,id) might be slightly faster than min(id) or max(id) as neither of them involve comparing the ids to find the min / max value. coalesce(id) is essentially elt(1,id), but I don't know if that's how it's actually implemented in the underlying code - if it is, then I expect one of the functions elt(1,id) and coalesce(id) might consistently be faster than the other.
By which I mean that if the underlying code handles the sql "coalesce(x)" as if it were the sql "elt(1,x)", then I'd expect a consistent relationship between the execution cycles used by the underlying code used to implement the two functions, but I have no idea which one would actually be faster.
Note that elt(n,x) will return null if n does not index a member of x, where elements of x are indexed as x[n=1] .. x[n=<whatever>].
> On Mon, 15 Oct 2012 15:23:25 +0200, Lennart Jonsson wrote:
>> On 10/15/2012 02:03 PM, Denis McMahon wrote: [...]
>>> select nom as name, coalesce(id) as id from nomid group by name;
>> Whats the purpose of coalesce (in this context)?
> coalesce(id) and elt(1,id) will both return the first id that appears in > the grouped list of ids for the name.
Ok, I think I see what you mean. coalesce(a_1, a_2, ..., a_n) is per
definition equal to:
case when a_1 is not null then a_1
when a_2 is not null then a_2
...
when a_n is not null then a_n
else null
end
in this case:
case when id is not null then id else null end
which is the same as:
id
But:
select id, name from T group by name
is illegal in sql92, sql99 loosens this restriction and allows the
construction iff id is f.d. of name. Mysql allows it by default (can be
fixed by via sanity properties). But, and this is why I asked:
select coalesce(id), name from T group by name
is equally illegal because coalesce is a function that maps one value
onto another (in contrast with for example min that maps a set of values
onto a value). Therefore:
On Tue, 16 Oct 2012 21:16:37 +0200, Lennart Jonsson wrote:
> select coalesce(id), name from T group by name
> is equally illegal because coalesce is a function that maps one value
> onto another (in contrast with for example min that maps a set of values
> onto a value).
Hmm
By my reading, coalesce(set_of_values) maps a set of values to a single value (the first member of the set).
Likewise elt(n,set_of_values) maps a set of values to a single value (the nth member of the set).
However, it appears that "select name, elt(2,id) as id from T group by name;" returns nulls for id, even where more than 2 ids are associated with a name, at which point understanding dawns.
Hence when I used elt(1,id) and coalesce(id) in "select name, elt(1,id) from T group by name;" and "select name, coalesce(id) from T group by name;" the "id" that was being passed to the elt of coalesce function wasn't the group of id values that would be passed to eg min or max, but rather a single id value as would be selected by "select id, name from T group by name;", so the latter is just as effective and probably more efficient than needlessly including elt or coalesce.
On Wed, 17 Oct 2012 02:01:24 +0000 (UTC), Denis McMahon wrote:
> On Tue, 16 Oct 2012 21:16:37 +0200, Lennart Jonsson wrote:
>> select coalesce(id), name from T group by name
>> is equally illegal because coalesce is a function that maps one value
>> onto another (in contrast with for example min that maps a set of values
>> onto a value).
> Hmm
> By my reading, coalesce(set_of_values) maps a set of values to a single > value (the first member of the set).
> Likewise elt(n,set_of_values) maps a set of values to a single value (the > nth member of the set).
> However, it appears that "select name, elt(2,id) as id from T group by > name;" returns nulls for id, even where more than 2 ids are associated > with a name, at which point understanding dawns.
> Hence when I used elt(1,id) and coalesce(id) in "select name, elt(1,id) > from T group by name;" and "select name, coalesce(id) from T group by > name;" the "id" that was being passed to the elt of coalesce function > wasn't the group of id values that would be passed to eg min or max, but > rather a single id value as would be selected by "select id, name from T > group by name;", so the latter is just as effective and probably more > efficient than needlessly including elt or coalesce.
the elt() examples tend to be kind of on the useless side, in the mysql
doco. A "real" use is to help deal with some "obvious" but poorly
normalized data.
SELECT a.respondent_id, q.question_text, elt(a.answer, q.answer_1, q.answer_2, q.answer_3)
FROM SURVEY_FORMAT Q join SURVEY_DATA A on q.qestion_id = a.question_id
-- A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting. [TOFU := text oben,
Q: What is the most annoying thing on usenet? followup unten]
