Problem with multiple records

• craigedmunds

  SSC Enthusiast

  Points: 149

  More actions

  February 17, 2003 at 9:42 am

  #79530

  I've got a slight problem at the start of a problem i'm trying to solve;

  i have a table of grid references for 'advisers'; tblAdviserGridReference:

  adviserId northing easting

  ---------- -------- -------

  2603L264Q0 01692 05412

  1936K028S0 01684 05462

  2603L264Q0 01695 05421

  obviously, an adviser could have an unlimited number of entries in this table.

  I'm then using a query to take a northing and easting, and calculate the distance between these two points, and sort by it;

  select adviserid,

  sqrt(square(northing-@northing) + square(easting-@easting))

  as distance

  from tblAdviserGridReference

  order by distance asc

  this works fine; giving the resultset;

  adviserid distance

  2603L264Q0 17.9375

  2603L264Q0 18.133726347609858

  1936K028S0 18.800452953320033

  for the easting and westing i entered (the values are irrelevant).

  The problem is i only want each 'adviserid' to appear once (to prevent duplication), and i preferably want the adviser with the shortest distance.

  I've been trying to figure this out all day; any help would be appreciated.

  cheers.

  c

• mromm

  SSCarpal Tunnel

  Points: 4301

  More actions

  February 17, 2003 at 10:49 am

  #449131

  select adviserId, MIN( SQRT( SQUARE( northing ) + SQUARE( easting ) ) )

  FROM #adviser

  GROUP BY adviserId