Return max sequence number of each group

[itprog]

say I have the following data set (Table1):

+----------+------------+-----------+------------+-----------+
| item | Line no | seqno | ord qty | del qty |
+----------+------------+-----------+------------+-----------+
| sls001 | 10 | 0 | 10 | 3 |
| sls001 | 10 | 1 | 7 | 5 |
| sls001 | 10 | 2 | 2 | 2 |
| sls013 | 20 | 0 | 10 | 10 |
| sls045 | 30 | 0 | 10 | 3 |
| sls045 | 30 | 1 | 7 | 0 |
| sls013 | 40 | 0 | 10 | 10 |
+------------------------------------------------------------------+


| sls001 | 10 | 2 | 2 | 2 |
I would like to group by "line no" and then select the line with the highest seq no in each group.
So in this case I would like to select the following lines:
| sls013 | 20 | 0 | 10 | 10 |
| sls045 | 30 | 1 | 7 | 0 |
| sls013 | 40 | 0 | 10 | 10 |

i did something like

domain tcpono l.seq
l.seq = 0
select item, line no, ord qty, del qty, MAX(seqno):l.seq
from Table1
where Table1.orno = {:Table2.orno}
group by line no, item, ord qty, del qty
selectdo
do some stuff......
endselect


but instead of selecting the lines with the highest "seqno" in each group, it selected every line where Table1.orno = {:Table2.orno} (the select do iterated for each record selected)

1) how do i make it so it only selects the lines with the highest "seqno" in each group

2) in the selectdo block, do I only have access to the field values that are in my select statement? Like if ord qty and del qty were not in my select statement, could i still use the ord qty and del qty values in the selectdo section?

[mark_h]

Well the group by has order qty, del qty in it so those values are different. Unless the values were the same I would expect different sequences to show up in the results.

Well I have always done it in next queries like this:

select table1.item, table1.line, max(table1.seq)
where ......
group by item, line
selectdo
     select a.ord.qty:ord.qty  a.del.qty:del.qty
     from table1 a
      where a.item = :table1.item
      and     a.line =  :table1.line
      and     a.seqno = :table.sequence
      as set with 1 rows
      selectdo
      endselect

      .....do other stuff.....
endselect

I always kept it simple for everyone to understand - especially me. :)