Hi,

Let's say I have a query like this.

select [table].*
from [table]
group by [table].[a-specific-field]

So this will result in giving out multiple records while group the [a-specific-field] together. However, I want to get another field in the group of that [a-specific-field] but only the first record -- something similar to

select min(...)
select max(...)

but is something like this

select first(...)

or even

select last(...)

I want to know if there is a way to do something like this.

Regards,
Thana

Mm, can you give a concrete example of how it should look like?

First record by what criteria/field?

select tfgld102.leac, tfgld102.dim1
from tfgld102
group by tfgld102.leac

Now that there might be many different values for tfgld102.dim1 per group of tfgld102.leac. I want to make it return the tfgld102.dim1 of the first record in the group of each tfgld102.leac.
Is it possible?

Example:
tfgld102.leac | tfgld102.dim1
10000 | 1
10000 | 2
10000 |
10000 | 3
20000 | 5
20000 | 8
20000 |
30000 | 15
30000 | 3

Desired result:
tfgld102.leac | tfgld102.dim1
10000 | 1
20000 | 5
30000 | 15

I want to make it return the tfgld102.dim1 of the first record in the group of each tfgld102.leac.
First record by what ordering criteria? There should be a 3rd field that "fixes" dim1 order within each leac group. Either way it is undoable within one select statement.

First record by what ordering criteria? There should be a 3rd field that "fixes" dim1 order within each leac group. Either way it is undoable within one select statement.

The first record that it finds with default ordering in the table (database).

Yeah, but I am not sure whether there is a default one (i. e. the ordering is consistent between multiple runs). I would like someone with more knowledge about Infor SQL implementation to confirm this.
You can try somethng like this:

select tfgld102.leac :leac.group
from tfgld102
group by tfgld102.leac
selectdo
select tfgld102.dim1
from tfgld102
where tfgld102.leac = :leac.group
as set with 1 rows
selectdo
*do something*
endselect
endselect

My requirement is changed and I no longer have to do this functionality so whoever is facing this issue should try giggty's suggestion.
Anyways, thank you for your help.