SQLite Forum

possible to use output of a SELECT-query for ordering?
Login

possible to use output of a SELECT-query for ordering?

(1) By tom (younique) on 2021-03-25 23:31:00 [source]

Hello,

is it possible to order a table by using the result of another query?

Example: CREATE TABLE a (b INTEGER, c INTEGER); INSERT INTO a VALUES (1,3),(2,7),(3,2),(5,1),(8,9),(9,3); SELECT * FROM a ORDER BY (SELECT 'c'); -- the constant 'c' is just for demonstration; this should eventually be a select from a different table

The manual says

Otherwise, if the ORDER BY expression is any other expression, it is evaluated and the returned value used to order the output rows. If the SELECT statement is a simple SELECT, then an ORDER BY may contain any arbitrary expressions.

To me it sounds as if it was possible, by it's not working for me.

(2.1) By Gerry Snyder (GSnyder) on 2021-03-25 23:46:02 edited from 2.0 in reply to 1 [link] [source]

I am not sure how what you want should work.

ORDER BY needs to be something that pertains to stuff in the outer SELECT.

Can you give an example of your real need?

(3) By Larry Brasfield (larrybr) on 2021-03-25 23:51:22 in reply to 1 [link] [source]

The "any other expression" or "arbitrary expressions" must still yield a value associated with each row to be sorted, if the sort is to have any effect. A constant value is not going to do anything useful.

If you can express your table with sorting values as an appended column, (with a join of some kind), then you can sort on the appended column and retain only the original table columns in the result set.

I suspect that you are imagining that the original table has some order, and that you can match that up with some other table also having some order then sort both together. It would be best to abandon thinking like that. Tables have no order beyond what can be imposed on result sets via ORDER BY.

(4.3) By Keith Medcalf (kmedcalf) on 2021-03-26 02:44:15 edited from 4.2 in reply to 1 [link] [source]

Works for me.

sqlite> create table x(x);
sqlite> insert into x select value from wholenumber where value between 1 and 10;
sqlite> create table y(x,y);
sqlite> insert into y select value, RandomV(10) from wholenumber where value between 1 and 10;
sqlite> select * from x;
┌────┐
│ x  │
├────┤
│ 1  │
│ 2  │
│ 3  │
│ 4  │
│ 5  │
│ 6  │
│ 7  │
│ 8  │
│ 9  │
│ 10 │
└────┘
sqlite> select * from y;
┌────┬───┐
│ x  │ y │
├────┼───┤
│ 1  │ 1 │
│ 2  │ 9 │
│ 3  │ 0 │
│ 4  │ 0 │
│ 5  │ 3 │
│ 6  │ 9 │
│ 7  │ 9 │
│ 8  │ 0 │
│ 9  │ 5 │
│ 10 │ 6 │
└────┴───┘
sqlite> select x from (select x, (select y from y where x.x == y.x) from x order by 2);
┌────┐
│ x  │
├────┤
│ 3  │
│ 4  │
│ 8  │
│ 1  │
│ 5  │
│ 9  │
│ 10 │
│ 2  │
│ 6  │
│ 7  │
└────┘
sqlite> select x from x order by (select y from y where y.x == x.x);
┌────┐
│ x  │
├────┤
│ 3  │
│ 4  │
│ 8  │
│ 1  │
│ 5  │
│ 9  │
│ 10 │
│ 2  │
│ 6  │
│ 7  │
└────┘
sqlite> select x.x from x join y on x.x == y.x order by y.y;
┌────┐
│ x  │
├────┤
│ 3  │
│ 4  │
│ 8  │
│ 1  │
│ 5  │
│ 9  │
│ 10 │
│ 2  │
│ 6  │
│ 7  │
└────┘
sqlite>

NB: wholenumber is the wholenumber virtual table add-in
NBB: RandomV is a custom add-in that returns a random integer

Edited to add a join candidate sort