Create a query using GROUP BY where only the first record is added

[Andre Beier]

Hi,

I have created a query with a GROUP BY statement
Here is an excerpt of the result set:

TankId Tank StartDate StartTime
13 Pilot 5/5/2004 8:00
13 Pilot 4/12/2004 12:00
13 Pilot 3/18/2004 10:40
1 S601 2/21/2004 15:21
1 S601 2/21/2004 15:21
1 S601 7/10/2004 17:31
11 S602 1/9/2004 0:00
11 S602 4/26/2004 8:00

I want to modify the query that only the FIRST record of each TankID Group
is displayed.
It should look like that:

13 Pilot 5/5/2004 8:00
1 S601 2/21/2004 15:21
11 S602 1/9/2004 0:00

Is there any way to do that in Access?

Thanks in advance

Andre

 

[Gerald Stanley]

It is hard to provide an answer without seeing the existing
SQL. Please repost with the code.

Gerald Stanley MCSD

 

[Andre Beier]

' =============================================================
SELECT tTank.TankId, tTank.TankCategory, tTank.TankName, vmaster.StartDate,
vmaster.StartTime
FROM tTank LEFT JOIN vmaster ON tTank.TankId = vmaster.Tank
GROUP BY tTank.TankId, tTank.TankCategory, tTank.TankName,
vmaster.StartDate, vmaster.StartTime
ORDER BY tTank.TankCategory, tTank.TankName, vmaster.StartDate DESC ,
vmaster.StartTime DESC;
' =============================================================

I tried to use TOP 1 but that only returns the very first item

e.g.
TankId Tank StartDate StartTime
13 Pilot 5/5/2004 8:00

 

[Gerald Stanley]

Try something along the lines of

SELECT DISTINCT tTank.TankId, tTank.TankCategory,
tTank.TankName, T1.StartDate, T1.StartTime
FROM tTank LEFT JOIN vmaster AS T1 ON tTank.TankId = T1.Tank
WHERE T1.startDate IN (SELECT Max(startDate) FROM vMaster
WHERE tank = T1.tank)
AND T1.startTime IN (SELECT TOP startTime FROM vMaster
WHERE tank = T1.tank ORDER BY startDate DESC, startTime DESC)
ORDER BY tTank.TankCategory, tTank.TankName, T1.StartDate
DESC , T1.StartTime DESC;

The second part of the WHERE clause is only necessary if
there is the possibility of multiple start times for the
same start date. If you know that it cannot happen, it can
be omitted.

Hope This Helps
Gerald Stanley MCSD