postgresql query returning in random order

Question

I am trying to create a SQL query that queries a database and returns info on various airports.

The basic query works fine, however the results are returned in a random order.

SELECT * FROM airportdata WHERE airportcode LIKE 'LHR' OR airportcode LIKE 'HKG'

It is really important that they are returned in the order passed into the query (i.e. the airport data from LHR be the first result returned followed by the airport info for HKG), however I can't get find any ORDER BY that will work.

apparently FIND_IN_SET is what I should be using, however I've tried all variations and I can't get it to work!

SELECT * FROM airportdata WHERE airportcode LIKE 'LHR' OR airportcode LIKE 'HKG' ORDER BY FIND_IN_SET(airportcode,'LHR,HKG')

Can anyone see what I'm doing wrong? Thanks!

"returned in the order passed into the query" — what does that mean? — Pointy
– Pointy, Commented Aug 26, 2018 at 12:16
And why are you using LIKE without any real pattern? Why isn't it just =? — Pointy
– Pointy, Commented Aug 26, 2018 at 12:19

Gordon Linoff · Accepted Answer · 2018-08-26 12:21:55Z

2

I would recommend that you use IN. You can then use a

SELECT *
FROM airportdata
WHERE airportcode IN ('LHR', 'HKG')
ORDER BY position(airportcode in 'LHR,HKG')

(This is safe because airport codes should be 3 characters and not have commas.)

If you want to use LIKE (which supports wildcards), you can do:

SELECT ad.*
FROM airportdata ad JOIN
     (VALUES ('LHR', 1),
             ('HKG', 2)
     ) v(pattern, pos)
     ON v.airportcode LIKE pattern
ORDER BY pos;

This does run the risk of duplicates, because one code could (theoretically) match multiple patterns. If that is a real risk, you would need to explain which pattern you prefer.

answered Aug 26, 2018 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahdi Aslami Khavari · Accepted Answer · 2018-08-26 12:19:45Z

1

use this query:

SELECT * 
FROM airportdata 
WHERE airportcode LIKE 'LHR' OR airportcode LIKE 'HKG' 
ORDER BY CASE WHEN airportcode LIKE 'LHR' THEN 1 ELSE 0 END

answered Aug 26, 2018 at 12:19

Mahdi Aslami Khavari

1,99119 silver badges27 bronze badges

Collectives™ on Stack Overflow

postgresql query returning in random order

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related