If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ackk...All Fileds with MIN
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 |
#2
|
|||
|
|||
Ackk...All Fileds with MIN
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) -- Ken Snell MS ACCESS MVP "AGP" wrote in message ... 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 |
#3
|
|||
|
|||
Ackk...All Fileds with MIN
That seems to work. I think i misunderstood the MIN syntax but your subquery
makes sense. Thanks. AGP "Ken Snell (MVP)" wrote in message ... 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) -- Ken Snell MS ACCESS MVP "AGP" wrote in message ... 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 |
#4
|
|||
|
|||
Ackk...All Fileds with MIN
"AGP" schrieb im Newsbeitrag ... 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 |
#5
|
|||
|
|||
Ackk...All Fileds with MIN
In article , "Eduard Schuller" wrote:
"AGP" schrieb im Newsbeitrag ... 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 SELECT TripId,RouteId,RouteName, RouteDist,RouteDesc,RouteNumStops,RouteIsMain FROM tblTrips WHERE TripId=200 AND RouteDist = (SELECT MIN(RouteDist) FROM tblTrips WHERE TripID = 200) |
Thread Tools | |
Display Modes | |
|
|