count duplicate records and select first 2 I have troubling issue where I want to only select the first 2 chunks of every dbspace created.
query used:
SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

Output:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

dbsnum  1
chknum  965
name    rootdbs
fname   /dev/informix/chunk1280

dbsnum  1
chknum  1099
name    rootdbs
fname   /dev/informix/chunk1281

What I want is:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

I am trying to put logic together to select the first 2 chunks created for example from each dbspace that exists. rootdbs has a total of 4 chunks I only want the first 2.
I can achieve this using a korn shell script:
for i in `echo &quot;output to pipe cat without headings select unique(dbsnum) from sysdbspaces&quot; | dbaccess sysmaster 2&gt; /dev/null | sed '/^$/d'`
do
fname=`echo &quot;output to pipe cat without headings select a.fname from (select 
first 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
AND a.dbsnum = '$i'
--AND a.is_temp = 1
GROUP BY
1,2,3,4
ORDER BY 1,2) a&quot; | dbaccess sysmaster 2&gt; /dev/null | sed '/^$/d'`
echo &quot;$fname&quot;
done
select * from (SELECT
    row_number() over (partition by 1,2,3,4 order by 1,2) as RowNo,
    a.dbsnum,
    b.chknum,
    a.name,
    b.fname
    FROM
    sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
    GROUP BY
    1,2,3,4) as innerSelect
where RowNo &lt;=2