CODEWITHSUNDEEP
sqlpostgresqlgroup-bydistinctpostgresql-9.5
user1591668
user1591668

Reputation: 2893

Postgres SQL How can I use group by and exclude the order by clause in it

I created a similar post and thought I had the answer but I didn't . What I would like to do is to get the nearest cities/towns given a certain latitudes and longitude while making the location column distinct . If you can see in my screenshot below the location of Orlando pops up twice, I would like to make that column distinct so that it can ignore the 2nd Orlando record . I am getting the nearest cities/towns correctly given the point of (28.458414,-81.395258) . The issue is that I have many records for big cities that have different coordinates within the same city, if you see I have slightly different coordinates for the 2 Orlando records . Any suggestion would be great . I am using postgres 10

SELECT location,ABS(28.458414 - latitudes) + ABS(-81.395258-longitudes) as distance FROM zips 
 group by location,latitudes,longitudes
 ORDER BY distance asc limit 5

I have also done

SELECT distinct location, ABS(28.458414 - latitudes) + ABS(-81.395258-longitudes) as distance FROM zips 
 ORDER BY distance asc limit 5

This is being used to show users options of near by towns and it would look wrong to show 2 towns/cities twice .

enter image description here

Upvotes: 1

Views: 1130

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Not sure on what basis you want to ignore the second Orlando record. If there was an ID column indicating the order of rows, you could get the highest or least using row_number() or DISTINCT ON. Using MIN or MAX may be an option you could try, removing latitudes and longitudes from group by

SELECT location,MIN(distance) as distance
FROM
(
  SELECT location, ABS(28.458414 - latitudes) + ABS(-81.395258 -longitudes )
   as distance FROM zips 
 ) group by location
 ORDER BY distance asc limit 5;

Upvotes: 3

Related Questions