Difference between revisions of "Informix SQL"
(→Select One Row in a Group) |
(No difference)
|
Latest revision as of 15:09, 15 May 2018
Select One Row in a Group
let's say you have the following table:
select groupid, name
from user_table;
groupid name
---------------
1 fred
2 cindy
2 hanna
3 willy
3 pat
3 harryYour goal: You want to select one member from each groupid regardless of who it is, like this:
groupid name
---------------
1 fred
2 cindy
3 willyIn Informix, every row has a unique rowid:
select rowid, groupid, name
from user_table
order by rowid;
rowid groupid name
-----------------------
1 1 fred
2 2 cindy
3 2 hanna
4 3 willy
5 3 pat
6 3 harrySo what you can do is select the minimum (or maximum) rowid to get only one row in a group by groupid:
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;
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:
select groupid, name, dob
from user_table;
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/1959We 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):
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;
Now we only get the oldest person in each groupid:
groupid name dob
---------------------------
1 fred 04/12/1986
2 cindy 12/22/1972
3 harry 05/05/1959
