Query to group, then get values associated to smallest grouped number

[barnaby]

Hi,

I asked this question a couple of months ago, and got a few replies but
I was unable to get the Access SQL/Query to work correctly in the
database model.

My Table is made up like this:

Building_ID, RoadSegment, RoadId, Distance

The database list is basically a list of the distances from buildings
to RoadSegments (if less than 300m away). Several RoadSegments make up
a RoadID There are hundreds of RoadSegments, and tens of RoadIDs.

I need:

* To perform a query so it picks the shortest distance from a each
building to each RoadID, stating which RoadSegment the shortest
distance refers to.

But to ensure:

* That the RoadSegment value picked actually refers to the shortest
distance
* That if the building is near more than one road, it picks up both
roads - and then finds the RoadSegment and Distance from the shortest
one on each.

I hope this makes sense. I've tried nested queries, but I can't seem to
make it work properly. Any help would be most gratefully received.

Regards,

Barnaby Burman

 

[Michel Walsh]

Hi,


SELECT BuildingID, RoadID, MIN(distance) As mdist
FROM table
GROUP BY BuildingID, RoadID



gives the minimal distance, for each couple (Building, Road). Save it as,
say, queryA.


SELECT a.buildingID, a.RoadID, FIRST(a.segmentID), FIRST(a.distance)
FROM table AS a INNER JOIN queryA AS b
ON a.buildingID = b.buildingID AND a.RoadID = b.RoadID AND
a.mdist=b.Distance
GROUP BY a.buildingID, a.RoadID



will pump back one of the segmentID that matches that min distance. Works
only with JET. In *this* case, you can replace FIRST by MIN to make it
works in MS SQL Server.


Hoping it may help,
Vanderghast, Access MVP