'Second' aggregate function

[Guest]

I have two fields Fld1 and Fld2, I want to group by Fld1 and find only the
second occurence of Fld2. I know that I can find the First and Last but when
I substituted 'First' by 'Second' in the SQL, it errored out. Any
suggestions?

 

[Tom Ellison]

Dear Neeraj:

If you want the "second" occurrence of Fld2 in the same sense that you
requested the First() occurrance, then no one can help you. From online
help:

"the records returned by these functions [First() and Last()] will be
arbitrary"

Now, the second arbitrary record is likely no more useful than the first.

I will therefore assume you want the second distinct value in alphabetical
or numeric order. A query for this would be:

SELECT Fld1,
(SELECT MIN(Fld2)
FROM YourTable T1
WHERE T1.Fld1 = T.Fld1
AND T1.Fld2 >
(SELECT MIN(Fld2)
FROM YourTable T2
WHERE T2.Fld1 = T1.Fld1)
FROM YourTable T

Replace your actual column and table names in the above.

Now, if your data looks like this:

Fld1 Fld2
AA BC
AA BC
AA DE

the result would be AA/DE. This is NOT the second but the third value. The
first two are both BC. Duplicates in this manner are a potential problem.
Do you have this problem and, if so, what do you want it to do?

Tom Ellison