Dedupe order by field

• bicky1980

  SSCrazy

  Points: 2079

  More actions

  September 23, 2010 at 7:08 am

  #228159

  Hello All

  I have a table which contains a postcode, source and a distance field

  Postcode,Source,Distance

  HX1 1AA,A,200

  HX1 1AA,B,220

  HX1 1AA,C,100

  HX1 1AA,D,120

  HX1 1AB,D,90

  HX1 1AC,D,300

  HX1 1AD,E,100

  HX1 1AD,E,200

  HX1 1AD,E,152

  I need to create a list containing a unique set of postcodes, with the applicable source and distance.

  The end Results of the above would need to be:

  HX1 1AA,C,100

  HX1 1AB,D,90

  HX1 1AC,D,300

  HX1 1AD,E,100

  I have a list of approx 500,000 records I neded to be perform this query on. Can anybody suggest the best way to do this.

  Any help would be gratefully appreciated...

  Thanks

  Ash

• kabi

  Valued Member

  Points: 64

  More actions

  September 23, 2010 at 8:02 am

  #1226339

  select a.* from

  (

  select Postcode,min(Distance) as Distance from Tablename group by Postcode

  ) as O inner join Tablename a on a.Postcode=O.Postcode and a.Distance =O.Distance