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?

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