I have a data like this


col1ID, ExpiryDate,col2ID
'000000000001998','2004-05-25 17:19:16.363','000000000000516'
'000000000001998','2004-08-11 14:19:46.939','000000000000517'
'000000000001998','2004-08-11 14:19:56.278','000000000000518'
'000000000001998','2004-08-11 14:20:05.137','000000000000515'
'000000000002001','2004-05-25 17:19:16.363','000000000000516'
'000000000002001','2004-08-03 14:17:20.206','000000000000518'
'000000000002145','2004-05-25 17:19:16.379','000000000000845'
'000000000003945','2004-05-25 17:19:16.629','000000000000516'
'000000000003945','2004-08-11 14:22:50.386','000000000000517'
'000000000003945','2004-08-11 14:23:07.996','000000000000514'
'000000000003996','2004-05-25 17:19:16.629','000000000000516'
'000000000003996','2004-07-23 14:03:55.513','000000000000516'
'000000000004114','2004-05-28 09:24:15.398','000000000000845'
'000000000004114','2004-05-28 11:16:17.070','000000000000845'
'000000000004273','2004-08-03 14:16:07.175','000000000000787'
'000000000004273','2004-07-26 12:56:17.114','000000000000787'
'000000000004273','2004-08-03 08:49:41.815','000000000000787'
'000000000004285','2004-08-03 14:16:03.393','000000000000787'
'000000000004285','2004-08-03 10:18:16.458','000000000000787'
'000000000004285','2004-08-03 10:19:17.803','000000000000787'
'000000000004285','2004-08-03 08:50:06.753','000000000000787'
'000030000000006','2004-05-25 17:19:16.644','000000000000516'
What I want is for each unique first col, I want to see the maxdate
for Expirydate and the first record for that date.
In the above data I like to see for first row.

'000000000001998','2004-08-11 14:20:05.137','000000000000515'
because it is the data for the max date field and the first col2id for
col1.

Second record should be
'000000000002001','2004-08-03 14:17:20.206','000000000000518'

and

'000000000003945','2004-08-11 14:23:07.996','000000000000514'

etc.,

Attempting like this

SELECT col1id,
max( ExpiryDate) as MaxREDate ,
(SELECT FIRST col2id
FROM table1 tab2
WHERE MaxREDate = tab2.ExpiryDate
AND tab1.col1ID = tab2.col1ID order by col2id) as scol2ID
FROM table1 tab2
group by col1id;


Then I am getting error like
"Function or column reference to 'expr_e' must also appear in a Group
By"

Using SA11, build 2376.

On 1 Feb., 17:37, Yum <officialinqu... (AT) gmail (DOT) com> wrote:

Select *,
(SELECT FIRST col2id
FROM table1 tab2
WHERE myTempTable.MaxREDate = tab2.ExpiryDate
AND myTempTable.col1ID = tab2.col1ID order by
col2id) as scol2ID
from ( SELECT col1id, max( ExpiryDate) as MaxREDate FROM table1
tab2*group by col1id ) as myTempTable