DISTINCT with other fields

[Scott Johnson]

I have a table such as:

<name>,<id>
Name1, id1
Name1, id2
Name1, id3
Name1, id4
Name2, 1d5
Name2, id6
Name2, id7
Name2, id8


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

[Scott Johnson]

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)

[Erick T. Barkhuis]

Scott Johnson:

>OK to answer my own question I found this.
>
>SELECT name, MIN(id) FROM table GROUP BY name;
>

This is correct, IF you actually want the smallest 'id' from the group
(which you didn't specify in your initial question).

--
Erick

[herman...@invalid.be.invalid]

Scott Johnson wrote:

> I have a table such as:
>
> <name>,<id>
> Name1, id1
> Name1, id2
> Name1, id3
> Name1, id4
> Name2, 1d5
> Name2, id6
> Name2, id7
> Name2, id8
>
>
> 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.

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)

[Erick T. Barkhuis]

herman...@invalid.be.invalid:

>Scott Johnson wrote:
>
>> I have a table such as:
>>
>> <name>,<id>
>> Name1, id1
>> Name1, id2
>> Name1, id3
>> Name1, id4
>> Name2, 1d5
>> Name2, id6
>> Name2, id7
>> Name2, id8
>>
>>
>> I only need one of the ID's per group of names.

[...]

>Well, in your setup, there is no such thing as THE id, you'll need
>some criterium to discern between these.

I agree, but...:

>
>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).

This I don't understand. Why would his 'id' column not be a "real
identifying column"?

[We agree that this will probably not be the real data, and it's
difficult to imagine a realistic business case for the forementioned
requirement]

--
Erick

[Denis McMahon]

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

[Scott Johnson]

Yes thanks. Yeah the sequence did not matter only at least one per group.

[Scott Johnson]

Thanks Denis I have not read on elt or coalesce, but will do so now.

[Lennart Jonsson]

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)?


/Lennart

[Brian Cryer]

"Scott Johnson" <noon...@chalupasworld.com> wrote in message
news:k5fp3s$9gv$1...@dont-email.me...

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.

I you find that you want the first record from each group - so more than
just the name and id then my notes here might be useful:
http://www.cryer.co.uk/brian/sql/sql_first_record_per_group.htm
--
Brian Cryer
http://www.cryer.co.uk/brian

[Scott Johnson]

Thanks that is some useful information.

Scotty

[Denis McMahon]

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>].

Rgds

Denis McMahon

[Lennart Jonsson]

On 2012-10-16 18:32, Denis McMahon wrote:
> 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:

select id, name from T group by name

could have been used instead

/Lennart

[Denis McMahon]

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.

Rgds

Denis McMahon

[Peter H. Coffin]

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.

DESCRIBE SURVEY_FORMAT
question_id int(8)
question_text varchar(35)
answer_1 varchar(15)
answer_2 varchar(15)
answer_3 varchar(15)

question_id question_text answer_1 answer_2 answer_3
----------- --------------- ----------- ----------- -----------
1 best pizza super-roni deluxe cheese
2 like interior yes no undecided
3 needs clowns maybe no hell no


DESCRIBE SURVEY_DATA
respondent_id int(8)
question_id int(8)
answer int(1) -- "1, 2, or 3"

respondent_id question_id answer
--------------- ----------- ------
1 1 3
2 1 2
2 2 3
2 3 3
3 3 2

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]