show first record from distinct(xxx) results

[mmy1981]

Hi,

I have a table "results" with 2 columns, "agentid" and "date". Records are:
agent1 - 29/03/2011 08:00:00
agent1 - 29/03/2011 08:30:00
agent1 - 29/03/2011 09:00:00
agent2 - 29/03/2011 08:10:00
agent2 - 29/03/2011 08:30:00

I would like to show the first record (or first date) for every agent -> So, the result would be 2 records:
agent1 - 29/03/2011 08:00:00
agent2 - 29/03/2011 08:10:00

SELECT TOP 1 .... only shows the first record. Hope anybody can help me...

 

[kermit01de]

Maybe this helps

SELECT results.agentid, First(results.date) AS FirstOfdate
FROM results
GROUP BY results.agentid;

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[mmy1981]

great,

I hope I can use this to get the final result (but it's already a step ahead, thx)

 

[PHV]

The First aggregate function is highly unreliable.
I'd use this instead:
[code}SELECT agentid, Min([date]) AS FirstOfdate
FROM results
GROUP BY agentid[/code]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmy1981]

I used MIN() instead of FIRST() since first showed the first record (not always the lowest date / time). Seems to work till now, I'm still combining it with the other queries.

I'll try the FirstOfdate too

SELECT results.agentid, First(results.date) AS FirstOfdateFROM resultsGROUP BY results.agentid;

 

[PHV]

OOps, sorry for the mess.
So, the First aggregate function is highly unreliable.

SELECT agentid, Min([date]) AS FirstOfdate
FROM results
GROUP BY agentid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kermit01de]

Min instead of first - yep, you are correct PHV. Sorry, I went wrong ...

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[PHV]

I'm still combining it with the other queries
And the problem is ?????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmy1981]

@PHV,

there's no problem anymore I didn't find the correct query for my initial question (the MIN(...) query). The other queries I needed where ok