SQLite Forum

select in order
Login

select in order

(1) By nyl (nylink) on 2020-04-16 03:40:09 [link] [source]

I have the following command:

SELECT * FROM products WHERE id IN (13,3,2,5,9,12,7)

it returns the results in ascending order

how do I get the results as I sent them ?

like:

1 record:

13

2 record:

3

3 record:

2

etc.

(2) By Gunter Hick (gunter_hick) on 2020-04-16 10:30:13 in reply to 1 [link] [source]

SQL is based on sets. Sets do not have an order. If you want results returned in a cetain order, you must specify an ORDER BY clause. Otherwise your resuts will be returned in "visitation order", which depends on the query and the query plan, and should not be relied on.

Why do you need to have the results in this particular order? Where does the list of id values come from?

Using the C API bindings (or a wrapper for whatever language you are using):

- call sqlite3_prepare() on the Statement SELECT * FROM prudcts WHERE id=?
- in a loop
  - call sqlite3_bind_integer() to bind the id
  - call sqlite3_step() to retrieve the record
- call sqlite3_finalize() to free up the Statement

With a temprorary table and ORDER BY:

CREATE TEMP TABLE sort_order (id INTEGER PRIMARY KEY, prod_id);
INSERT INTO sort order (prod_id) VALUES (13),(3),(2),(5),(9),(12),(7);
SELECT p.* FROM sort_order s JOIN products p ON s.prod_id = p.id ORDER BY s.id;

Using a CTE and implicit evaluation order (NOTE: UNION ALL is required, as UNION alone would create an epehmeral table to eliminate duplicates and return them in ascending order)

WITH ids (id) AS (SELECT 13 UNION ALL SELECT 3 …) SELECT p.* from ids i CROSS JOIN products p ON p.id = s.id;

(3) By ddevienne on 2020-04-16 10:51:59 in reply to 2 [link] [source]

Another way, not any more practical than the others Gunter already provided, just for fun:

sqlite> create table p (id integer primary key, other);
sqlite> insert into p values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');
sqlite> .mode col
sqlite> .header on
sqlite> select * from p; -- insert order (i.e. rowid PK)
id          other
----------  ----------
1           one
2           two
3           three
4           four
sqlite> select * from p where id in (3,2,4); -- arbitrary order returned
id          other
----------  ----------
2           two
3           three
4           four
sqlite> select * from p where id in (3,2,4)
   ...> order by case id when 3 then 1 when 2 then 2 when 4 then 3 end;
id          other
----------  ----------
3           three
2           two
4           four
sqlite>

(19) By Keith Medcalf (kmedcalf) on 2020-04-17 06:15:03 in reply to 3 [link] [source]

Using data that looks like this:

create table p (id integer primary key, other);
insert into p values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four');

You could use a query like this:

with ids (id) 
  as (
      values (3),
             (2),
             (4)
     ) 
     select p.* 
      from ids 
cross join p 
     where p.id == ids.id;

or like

  select p.*
    from (
          values (3),
                 (2),
                 (4)
         ) as ids
cross join p
     where p.id == ids.column1;

both of which only require you to build the values list once ...

(4) By ddevienne on 2020-04-16 10:59:59 in reply to 2 [link] [source]

Still for fun, yet another one, via the JSON1 extension, whose benefit is that one writes the p.id array only once
(albeit as a string, requiring parsing):

sqlite> select p.* from p join json_each('[3,2,4]') a on p.id = a.value order by a.key;
id          other
----------  ----------
3           three
2           two
4           four

(5) By Tony Papadimitriou (tonyp) on 2020-04-16 11:48:50 in reply to 4 [link] [source]

This is very useful.

How would one use json_each() with strings instead of numbers? I couldn't figure out the syntax.

(12) By ddevienne on 2020-04-16 16:50:55 in reply to 5 [link] [source]

What did you try? It's no different. JSON strings use double-quotes:

sqlite> create unique index p_other_unique on p(other);
sqlite> select p.* from p join json_each('["three","two","four"]') a on p.other = a.value order by a.key;
id          other
----------  ----------
3           three
2           two
4           four

(8) By nyl (nylink) on 2020-04-16 16:05:43 in reply to 4 [link] [source]

Gunter there are obvious reasons why you would need the results in the order you submitted them

you need to be able to track your results as you receive them

having them returned in ASC order unless specified, doesn't make sense


ddevienne thanks a lot, great solution !

(18) By Gunter Hick (gunter_hick) on 2020-04-17 05:50:24 in reply to 8 [link] [source]

Pretend I were stupid and explain the obvious reasons.

Begin by stating the programming language you are using and the schema of your table and any associated indeces.

Then answer the question I already asked: Where does the list of ids come from?

You must already be prepared to handle the case of deleted rows. Pretend only odd ids exist. What happens when you expect record 2 but you get record 5 instead? What happens when there are no morw rows in the result set but you still have ids in your list?

Surely there must exist a way of linking a row of data with the id used to retrieve it. And even if all you have is pipes connected to the input and output streams of a process running the SQLite shell program, you can always delimit the result set witg "guard queries", e.g.:

select 'BEGIN_QUERY_1';
<your query>
select 'END_QUERY_1';

(24) By anonymous on 2020-04-17 19:57:25 in reply to 18 [link] [source]

And even if all you have is pipes connected to the input and output streams of a process running the SQLite shell program, you can always delimit the result set with "guard queries", e.g.:

select 'BEGIN_QUERY_1';
<your query>
select 'END_QUERY_1';

I have done something else (for use with Node.js): sqlite3 -quote -batch -cmd '.explain off' And then after each query, use the dot command: .print * (My program also parses host parameters and substitutes their values, quoted properly for use in SQL.)

I should think that would work much better, because in the other case, there is the possibility that one of the result of the query is END_QUERY_1, so then it will be confused. Also, -quote mode allows the external program to use the data types properly, too.

But, while the above works in Node.js, it does not work in Ghostscript, whose %pipe% device is only a one way pipe (like popen() normally is), not two ways like in Node.js. So, now the other thing I would want to have is to be able to access SQLite from PostScript programs too, somehow.

(6) By doug (doug9forester) on 2020-04-16 15:37:24 in reply to 1 [link] [source]

Yet another simple way:

sqlite> create table p (id integer primary key autoincrement, other);
sqlite> insert into p values (null,'one'), (null,'two'), (null,'three'), (null,'four');
sqlite> select id,other from p order by id;
id      other
1       one
2       two
3       three
4       four
(I'd like to be able to insert into the table without having to put in the placeholder for the id, but I can't figure out how.)

(7) By Keith Medcalf (kmedcalf) on 2020-04-16 15:57:37 in reply to 6 [link] [source]

insert into p (other) values ('one'),('two'),('three'),('four');

And why did you declare id as 'integer primary key autoincrement'? Do you require the extra overhead/functionality associated with autoincrement or are you just including it because it sounds nice?

https://sqlite.org/autoinc.html

(11) By doug (doug9forester) on 2020-04-16 16:43:32 in reply to 7 [link] [source]

OMG! insert into p(other) is obviously the way to not have to specify the id.

OTOH I thought that autoincrement was the safe way to guarantee that the id's were always incrementing. If you delete some records, can't a rowid table reuse those numbers?

(20) By Keith Medcalf (kmedcalf) on 2020-04-17 06:26:24 in reply to 11 [link] [source]

Yes. However, any new inserted records will always have an auto-generated id at least one greater than the greatest id that exists in the table at the time of insert -- unless of course you overflow the 64-bit signed id in which case a random "hole" will be used if it can be found in a reasonable time.

Contrast this with "autoincrement" which will always have an auto-generated id at least one greater than the greatest id that ever existed in the table at the time of insert -- and will give an "table full" error when the 64-bit signed integer overflows.

So without autoincrement if you do
insert -- auto id 1
insert -- auto id 2
insert -- auto id 3
delete where id == 3
insert -- auto id 3
delete where id == 2
insert -- auto id 4

  • resulting records 1,3,4

vs with autoincrement
insert -- auto id 1
insert -- auto id 2
insert -- auto id 3
delete where id == 3
insert -- auto id 4
delete where id == 2
insert -- auto id 5

  • resulting records 1,4,5

(21) By ddevienne on 2020-04-17 08:18:32 in reply to 20 [link] [source]

and will give an "table full" error when the 64-bit signed integer overflows

Given that (2^63)/60/60/24/365/1e6 = 292471.2, seems to me one needs
at least 292K row mutations per microsecond for a full year to overflow
that signed 64-bit int, can we all agree that's not a problem in practice?

(22) By Larry Brasfield (LarryBrasfield) on 2020-04-17 12:16:11 in reply to 21 [link] [source]

Re row mutations every few picoSeconds for a year not a problem in practice:

Not today. But computing speed has increased immensely, and somewhat logarithmically over the years. At the order of magnitude rate of increase I've seen, that problem needs only another hundred years or so to become a concern.

(23) By ddevienne on 2020-04-17 18:03:55 in reply to 22 [source]

Write speed to persistent media is not Moore Law...

Even today's SSD seek time is around 0.1 ms, according to Wikipedia.
Just compute the energy necessary to power the CPU and Disk necessary
to do all those writes (a stagering number), and you can see we're unlikely
to see one of those int64 integers wrap around in our lifetimes.

There are way more things to worry out that such 64-bit wrap arounds!

(25) By Peter da Silva (resuna) on 2020-06-04 17:46:32 in reply to 23 [link] [source]

Try writing into tmpfs. That's a bit faster than SSD.

(26) By Keith Medcalf (kmedcalf) on 2020-06-04 23:26:39 in reply to 22 [link] [source]

Computing speed is not a relevant consideration. Just because you CAN insert a bazillion rows per second does not mean you WILL insert a bazillion rows per second.

There are other limits on the rate at which rows can be inserted that have nothing to do with computing speed. For example, if you added a row for every person that was "born" it is not relevant how fast you can insert records (as long as it is fast enough), the limiting factor is the rate breeding rate of humans populating the planet.

Similarly the rate of insertion into the customer table is based on the growth rate of customers and not the speed of the computing device. So while you may be able in a hundred years to be able to acquire customers at a rate that would allow you to exceed the limit, that fact is irrelevant, since it is not that limit that is meaningful.

The "speed" of the computing device has little, if anything, to do with it in any real sense.

(9) By Mark Lawrence (mark) on 2020-04-16 16:33:47 in reply to 1 [link] [source]

An alternative that doesn't use a second table or make you increment numbers for a CASE statement:

SELECT
    *
FROM
    products
WHERE
    id IN (13,3,2,5,9,12,7)
ORDER BY
    id=13 DESC,
    id=3 DESC,
    id=2 DESC,
    id=5 DESC,
    id=9 DESC,
    id=12 DESC,
    id=7 DESC
;

(10) By nyl (nylink) on 2020-04-16 16:35:48 in reply to 9 [link] [source]

great! thank you!

(13) By Larry Brasfield (LarryBrasfield) on 2020-04-16 16:55:02 in reply to 9 [link] [source]

That ORDER BY clause is something new to me. I did not know it was SQL or what it did, even after some research. Can you briefly explain how/why it works?

(14.2) By ddevienne on 2020-04-16 17:05:36 edited from 14.1 in reply to 13 [link] [source]

You need to play with JSON1. json_each is a table-valued function
which generates rows on the fly, and one of the column, for arrays,
is the index into the array. And I simply order on that.

Oops, sorry, I thought you meant my technique :)

Regarding the other technique, id=13 is a boolean expression,
and true sorts after false, thus the DESC to reverse that,
so it's like sorting these tupples (1,0,...), (0,1,0,...), etc...

That's pretty twisted :) In an interesting way I mean!

(15) By Larry Brasfield (LarryBrasfield) on 2020-04-16 17:13:04 in reply to 14.1 [link] [source]

That json_each trick is interesting, and perhaps valuable after I somehow shed my reluctance to treat stuff in a DB as sometimes single objects and sometimes a sequence of objects. It feels like wandering toward the dark side, with little clear warning that you're there other than relentless headaches. I'll have to play with it, where giving up is an option.

(16) By Keith Medcalf (kmedcalf) on 2020-04-16 17:21:12 in reply to 13 [link] [source]

ORDER BY can specify multiple things to sort by.
In this case, there is one thing to sort by for each ID.

When the ID is 13 you are sorting by 1,0,0,0,0,0,0
When the ID is 3 you are sorting by 0,1,0,0,0,0,0
When the ID is 2 you are sorting by 0,0,1,0,0,0,0
and so on.

The id = <something> in the order by is an equal comparison, not assignment.

(17) By Larry Brasfield (LarryBrasfield) on 2020-04-16 17:51:38 in reply to 16 [link] [source]

Yes, of course. Thanks. I was sillily trying to read 0,0,0,1,0,0,0 as more magical than it is when considered as the ORDER BY syntax and meaning demands.