Ackk...All Fileds with MIN

[Database Administrator]

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

[Database Administrator]

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



"AGP" wrote in message
news:INlKk.5207$be.5017@nlpi061.nbdc.sbc.com...
>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
>

[Database Administrator]

That seems to work. I think i misunderstood the MIN syntax but your subquery
makes sense.
Thanks.

AGP

"Ken Snell (MVP)" wrote in message
news:%23MfBDITMJHA.4540@TK2MSFTNGP05.phx.gbl...
> 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
>
>
>
> "AGP" wrote in message
> news:INlKk.5207$be.5017@nlpi061.nbdc.sbc.com...
>>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
>>
>
>

[Database Administrator]

"AGP" schrieb im Newsbeitrag
news:INlKk.5207$be.5017@nlpi061.nbdc.sbc.com...
>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
>

[Database Administrator]

In article <93E3F4D1-3D4E-4736-A682-6B21D7840D4C@microsoft.com>, "Eduard Schuller" wrote:
>
>"AGP" schrieb im Newsbeitrag
>news:INlKk.5207$be.5017@nlpi061.nbdc.sbc.com...
>>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)