select statement order by top 3 from one column and the rest by another column

Question

I need to use two different sortorders. I want to select top 3 based on column PopularitySortOrder ASC and the rest by SortOrder ASC.

Table

---------------------------------------------------------------------------------------
|   Id   |      Product         |   Price   |   SortOrder   |   PopularitySortOrder   |
---------------------------------------------------------------------------------------
    1     Samsung Galaxy S6 Edge     100            1                   2 
    2          iPhone 6              100            2                   1 
    3          iPhone 5S             100            4                   4 
    4       Samsung Galaxy S6        100            6                   3 
    5       Google Nexus 6           100            3                   5 
    6       Google Nexus 5           100            5                   7 

I've tried with following select, unfourtanly unsuccessful:

select *
FROM
(
   select * from Temp1 t1 order by PopularitySortOrder LIMIT 3
   union all
   select * from Temp1 t2 where t2.Id <> t1.Id order by SortOrder
) 
order by PopularitySortOrder, SortOrder asc

Expected result:

---------------------------------------------------------------------------------------
|   Id   |      Product         |   Price   |   SortOrder   |   PopularitySortOrder   |
---------------------------------------------------------------------------------------
    2          iPhone 6              100            2                   1 *
    1     Samsung Galaxy S6 Edge     100            1                   2 *
    4       Samsung Galaxy S6        100            6                   3 *
    5       Google Nexus 6           100            3 *                 5 
    3          iPhone 5S             100            4 *                 4 
    6       Google Nexus 5           100            5 *                 7 

EDIT: Using SQLite version 3.7

Fiddle

Answer 1

t1 is not visible in the other part of the UNION query.

You can duplicate the popularity query, or use a temporary view, or (in SQLite 3.8.3 or later) a common table expression:

WITH MostPopular3 AS (
  SELECT * FROM Temp
  ORDER BY PopularitySortOrder
  LIMIT 3
)
SELECT * FROM MostPopular3
UNION ALL
SELECT * FROM (
  SELECT * FROM Temp
  WHERE Id NOT IN (SELECT Id FROM MostPopular3)
  ORDER BY SortOrder
)