0

I am new to Postgresql Database...i don't know what is problem but below query with Distinct results non-distinct results. please help on this issue.

 SELECT 
   Distinct(LEAST(name_1, name_2))||GREATEST(name_1, name_2) as keycol,
   name_1, 
   name_2, 
   latitude, 
   longitude 
 FROM england_intersection 
 order by keycol
 limit 100;`

Output result: first field name is kaycol

"007 DriveBroccoli Road";"007 Drive";"Broccoli Road";51.5495845;-0.5349483
"007 DriveBroccoli Road";"Broccoli Road";"007 Drive";51.5495845;-0.5349483
"007 DriveGoldfinger Avenue";"007 Drive";"Goldfinger Avenue";51.5481323;-0.5347869
"007 DriveGoldfinger Avenue";"Goldfinger Avenue";"007 Drive";51.5481323;-0.5347869
"018Stour Valley Way";"018";"Stour Valley Way";50.7637982;-1.8722392
"018Stour Valley Way";"Stour Valley Way";"018";50.7637982;-1.8722392
"018Wimborne Road";"018";"Wimborne Road";50.7622107;-1.8746797
"018Wimborne Road";"Wimborne Road";"018";50.7622107;-1.8746797`

should return one of below

"10095";"100";"95";51.1280481;1.3347689
"10095";"95";"100";51.1280481;1.3347689

should return one of below

"10096";"100";"96";51.1280481;1.3347689
"10096";"96";"100";51.1280481;1.3347689`
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1586957
  • 149
  • 2
  • 13
  • I removed the MySQL tag, as this is a PostgreSQL question – Arth Aug 01 '14 at 13:38
  • 1
    `distinct` is ***not*** a function. It always operates on all columns in the select list. Your expression `(LEAST(name_1, name_2))||GREATEST(name_1, name_2)` simply creates an anonymous object type (which is a single column in the select list). See here: http://stackoverflow.com/a/25059838/330315 to understand what those parentheses around the columns are really doing. –  Aug 01 '14 at 13:41

0 Answers0