On 2018/03/09 3:14 PM, Hegde, Deepakakumar (D.) wrote:
Hi All,
We have a problem as below:
we have created a table as below:
CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;
We have inserted 5 entry to this table, and ID will be from 1 to 5 as below
ID NAME
1 ABC
2 AAA
3 CBA
4 BAC
5 BBB
We execute following select statetment:
SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);
output for above is:
ID NAME
1 ABC
2 AAA
3 CBA
It seems by default sqlite is getting the entry in the order of primary key or
rowid.
Yes, as it should - A "set" has no inherent order to it - that IN
statement is equivalent to saying:
Show the name if it has an ID found in this bag of goodies: ( car, 2,
lemon, three, 3, tree, 1 ) which is exactly the same as this bag: ( 3,
1, lemon, tree, 2, car, three ) - the order does not matter.
What you see is the order in which the items get selected to check if
they have an ID in the bag - that is usually (but not always) the
primary key order.
If you need anything to be ordered, you have to specify the order.
So for us expected output is:
ID NAME
3 CBA
1 ABC
2 AAA
Is there anyway to do this without adding a new column? with the same table?
Usually specifying the order is easy, but you seem to want to make up
the order as you go - this can still be done, but is more technical.
Here is an example joining to a subquery that sets up sort and sid
fields so you can specify both the ID to join and the sort order in
which it should be produced. The part after the VALUES is all you need
to adjust.
SELECT *
FROM NEWFOLDER
JOIN (SELECT -1 sort, -1 sid UNION ALL VALUES (1,3), (2,1), (3,2)
) AS X ON ID = X.sid
ORDER BY X.sort
Here it is working with Aliasing to produce specific columns only:
-- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed
version 2.0.2.4.
--
================================================================================================
CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;
INSERT INTO NEWFOLDER(ID, NAME) VALUES
(1, 'ABC')
,(2, 'AAA')
,(3, 'CBA')
,(4, 'BAC')
,(5, 'BBB')
;
SELECT A.*
FROM NEWFOLDER AS A
JOIN (SELECT -1 sort, -1 sid UNION ALL VALUES (1,3), (2,1), (3,2)) AS
X ON A.ID = X.sid
ORDER BY X.sort
-- ID | NAME
-- --- | ----
-- 3 | CBA
-- 1 | ABC
-- 2 | AAA
we need a way where by we can get the entry as we given in "where" "in" clause
That is impossible.
Thanks and Regards
Deepak
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
