Ackk...All Fileds with MIN

[AGP]

I am trying to construct a SQL statement to return records with a minimum
value in a field. I've been reading on how to use the MIN syntax but cannot
figure out how to return all fields in my results. The table looks like so:

TripId RouteId RouteName RouteDist RouteDesc RouteNumStops
RouteIsMain
200 1 A1 100 A.ES.34
3 F
200 2 A4 110 A.ES.36
3 T
200 3 D5 200 A.ES.37
2 F
200 4 E7 152 A.ES.10
3 F
350 1 Z4 440 Z.ES.34
3 T
350 2 SS 425 Q.ES.11
3 F
350 3 D0 495 Q.ES.44
2 F
350 4 WW 425 S.ES.10 3
F

I know my TripId and im trying to return a record with all fields with a
minimum RouteDist so am doing something like:

SELECT TripId,RouteId,RouteName, MIN(RouteDist),
RouteDesc,RouteNumStops,RouteIsMain
FROM tblTrips
WHERE TripId=200
GROUP BY TripId

I am anticipating this for TripId=200
200 1 A1 100 A.ES.34
3 F
and this for TripId=350
350 2 SS 425 Q.ES.11
3 F
350 4 WW 425 S.ES.10 3
F

But I get an error about not including the other fields as part of the group
or an aggregate function.is there a way to get all fields as a return result
with the criteria that a filed is at a minimum value? i just cant figure out
how to do it with one statement. i can do it in two statements but it seems
there should be a more straightforward way to do it.

tia for any suggestions
AGP

 

[Ken Snell \(MVP\)]

Try this -- it uses a subquery in the WHERE clause:

SELECT TripId,RouteId,RouteName, RouteDist,
RouteDesc,RouteNumStops,RouteIsMain
FROM tblTrips
WHERE TripId=200 And
RouteDist =
(SELECT Min(T.RouteDist)
FROM tblTrips AS T
WHERE T.TripID = tblTrips.TripID)

 

[Eduard Schuller]

I am trying to construct a SQL statement to return records with a minimum
value in a field. I've been reading on how to use the MIN syntax but cannot
figure out how to return all fields in my results. The table looks like so:

TripId RouteId RouteName RouteDist RouteDesc RouteNumStops
RouteIsMain
200 1 A1 100 A.ES.34 3
F
200 2 A4 110 A.ES.36 3
T
200 3 D5 200 A.ES.37 2
F
200 4 E7 152 A.ES.10 3
F
350 1 Z4 440 Z.ES.34 3
T
350 2 SS 425 Q.ES.11 3
F
350 3 D0 495 Q.ES.44 2
F
350 4 WW 425 S.ES.10
3 F

I know my TripId and im trying to return a record with all fields with a
minimum RouteDist so am doing something like:

SELECT TripId,RouteId,RouteName, MIN(RouteDist),
RouteDesc,RouteNumStops,RouteIsMain
FROM tblTrips
WHERE TripId=200
GROUP BY TripId

I am anticipating this for TripId=200
200 1 A1 100 A.ES.34 3
F
and this for TripId=350
350 2 SS 425 Q.ES.11 3
F
350 4 WW 425 S.ES.10
3 F

But I get an error about not including the other fields as part of the
group or an aggregate function.is there a way to get all fields as a
return result with the criteria that a filed is at a minimum value? i just
cant figure out how to do it with one statement. i can do it in two
statements but it seems there should be a more straightforward way to do
it.

tia for any suggestions
AGP

 

[Brian]

SELECT TripId,RouteId,RouteName, RouteDist,RouteDesc,RouteNumStops,RouteIsMain
FROM tblTrips
WHERE TripId=200
AND RouteDist = (SELECT MIN(RouteDist) FROM tblTrips WHERE TripID = 200)