2

I try to select from ne_10m_populated_places both the 15 biggest cities together with the 3~5 ones where FEATURECLA='Admin-0 capital' (the countries capitals). So for my area, I should get back ~18-20 places. I'am very beginner to SQL, and don't know where how basic operators chain up. I tried:

SELECT * FROM ne_10m_populated_places ORDER BY POP_MAX DESC LIMIT '15' 
OR WHERE FEATURECLA='Admin-0 capital'

The first line works, but it fails when I add it the second line. Help welcome !

EDIT: no working answer yet.

Hugolpz
  • 17,296
  • 26
  • 100
  • 187

1 Answers1

2

The ordering of your statements is incorrect and you need to do a UNION if you want to add specific results.

In this example, we find the 15 most populated cities and then UNION specific cities that you've specified.

In the first query of the UNION, it is turned into a sub-select since ORDER BY will affect the entire UNION otherwise.

SELECT * 
FROM (
    SELECT * 
    FROM ne_10m_populated_places
    ORDER BY POP_MAX DESC 
    LIMIT 15
)

UNION 

SELECT * 
FROM ne_10m_populated_places
WHERE FEATURECLA = 'Admin-0 capital'
Eric Hotinger
  • 8,957
  • 5
  • 36
  • 43
  • 1
    @Doon - just turned it into a subselect. Should work now I think ... let me know if you find any issues with it since I don't have SQLite handy. – Eric Hotinger May 15 '15 at 17:16
  • Beat me to it, Was going to say subselect *should* work. (at least it parses now) – Doon May 15 '15 at 17:18
  • Wow, I was really far from It. I'am testing it. – Hugolpz May 15 '15 at 17:19
  • This doesn't work for me unless the subquery is assigned a name. To be fair, I'm testing on SQL Server and not SQLite, which has very slightly different syntax (`SELECT TOP 15 *` instead of `LIMIT 15` at bottom) – Muhammad Abdul-Rahim May 15 '15 at 17:20
  • @Hugolpz well it isn't that "basic" of a query (due to order / limit on only part of the results) – Doon May 15 '15 at 17:20
  • @MariM yeah. TSQL would be a UnionAll and slightly different syntax, see http://stackoverflow.com/questions/3685563/sql-server-order-by-in-subquery-with-union – Doon May 15 '15 at 17:22
  • Indeed, I get only 12 results made of all the capitals (as expected) but far less populated places than expected. I try to find why. – Hugolpz May 15 '15 at 17:27
  • `SELECT * FROM ne_10m_populated_places ORDER BY POP_MAX DESC LIMIT 15` get back 15 biggest cities, white `SELECT * FROM ( SELECT * FROM ne_10m_populated_places ORDER BY POP_MAX DESC LIMIT 15 )` get back 4 cities. – Hugolpz May 15 '15 at 17:34
  • Hahaha. Ok. I'am on the other page provided by Doon. Trouble is much harder than though ! – Hugolpz May 15 '15 at 17:40
  • @Hugolpz are you sure., seems to work as demonstrated here http://sqlfiddle.com/#!5/a9734/2/0 – Doon May 15 '15 at 18:26