Beard: /* Select One Row in a Group */

2018-05-15T19:09:54Z

‎Select One Row in a Group

New page

== Select One Row in a Group ==

let's say you have the following table:

<syntaxhighlight lang="sql">
select groupid, name
from user_table;
</syntaxhighlight>
<syntaxhighlight>
groupid name
---------------
1 fred
2 cindy
2 hanna
3 willy
3 pat
3 harry
</syntaxhighlight>

<big>Your goal:</big> You want to select one member from each groupid regardless of who it is, like this:
<syntaxhighlight>
groupid name
---------------
1 fred
2 cindy
3 willy
</syntaxhighlight>

In Informix, every row has a unique rowid:
<syntaxhighlight lang="sql">
select rowid, groupid, name
from user_table
order by rowid;
</syntaxhighlight>
<syntaxhighlight>
rowid groupid name
-----------------------
1 1 fred
2 2 cindy
3 2 hanna
4 3 willy
5 3 pat
6 3 harry
</syntaxhighlight>

So what you can do is select the minimum (or maximum) rowid to get only one row in a group by groupid:

<syntaxhighlight lang="sql">
select a.groupid, a.name
from user_table a,
(select min(rowid) rid, groupid
from user_table
group by groupid) b
where a.rowid = b.rid
order by a.groupid;
</syntaxhighlight>
This will give you the results you want.

Notice that I renamed rowid in the subquery to rid. If you don't do this you will get an error.

Now let's say we DO care about which row we select from each group. Maybe we want to select the oldest person. So with a table like this:
<syntaxhighlight lang="sql">
select groupid, name, dob
from user_table;
</syntaxhighlight>
<syntaxhighlight>
groupid name dob
---------------------------
1 fred 04/12/1986
2 cindy 12/22/1972
2 hanna 01/15/1983
3 willy 10/09/1977
3 pat 04/11/1980
3 harry 05/05/1959
</syntaxhighlight>

We can add an order by statement in the subquery. Just make sure you also add the dob column to the select and group by statements as well (in the subquery):
<syntaxhighlight lang="sql">
select a.groupid, a.name
from user_table a,
(select min(rowid) rid, groupid, dob
from user_table
group by groupid, dob
order by dob asc) b
where a.rowid = b.rid
order by a.groupid;
</syntaxhighlight>

Now we only get the oldest person in each groupid:
<syntaxhighlight>
groupid name dob
---------------------------
1 fred 04/12/1986
2 cindy 12/22/1972
3 harry 05/05/1959
</syntaxhighlight>

Informix SQL - Revision history

Beard: /* Select One Row in a Group */