Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
On 3/9/18, Hegde, Deepakakumar (D.) wrote: > > SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2); > Here is a query that gives the rows in the order you desire: WITH a(x,y) AS (VALUES(3,1),(1,2),(2,3)) SELECT newfolder.* FROM newfolder, a WHERE x=id ORDER BY y; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
If the prefered ORDER BY clause is awkward;
How large is your table?
and is it on a Solid State Disk (SSD) with low seek time?
If the table is small (less than 100,000 rows) and you are querying by an
indexed field (such as the Primary Key)
you could just do three (or N) SELECT statements to guarantee the order.
SELECT * FROM NEWFOLDER WHERE ID = 3;
SELECT * FROM NEWFOLDER WHERE ID = 1;
SELECT * FROM NEWFOLDER WHERE ID = 2;
This is NOT efficient, but it is what transaction processing systems do all
day with randomly arriving known customers.
If you need the results combined in one data structure (for all the values
of ID) you could make this more elaborate with a UNION query
or you could assemble the data in the language that you are calling SQL
from (assuming you are not using the command line interface). If you are
using the command line interface you could redirect to a file and append
(">" and ">>").
Jim Callahan
Callahan Data Science LLC
Orlando, FL
Virus-free.
www.avast.com
<#m_-7888910753152976491_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
On Fri, Mar 9, 2018 at 8:14 AM, Hegde, Deepakakumar (D.) <
deep...@allgosystems.com> 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.
>
>
> 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?
>
> we need a way where by we can get the entry as we given in "where" "in"
> clause
>
>
> Thanks and Regards
>
> Deepak
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
On 3/9/18, Hegde, Deepakakumar (D.) wrote: > > So for us expected output is: If your query does not have an ORDER BY clause, then SQLite (and every other SQL database engine) is free to return the result rows in any order it wants. At this point in history, SQLite happens, by chance, to return the rows in rowid order for your particular query. But that might change with the next release. Or it might change with shifts in the jet stream. You never know. The point is that you must never depend on a particular row order from a SELECT statement that lacks an ORDER BY clause. If you need to output rows in a particular order, then please devise an ORDER BY clause that expresses that ordering. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
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
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
Sets of things inherently have no order. Since you have not specified an order (as in an order by clause), any ordering you perceive is simply a figment of your imagination and does not, in reality, exist. You can always add another column and put your order in it so that you can sort by that column, or retrieve the rows one at a time so that your application can deal with them sequentially. However, set-based data management systems do not have any inherent order in sets of rows processed ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar >(D.) >Sent: Friday, 9 March, 2018 06:15 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] How to get the entry from sqlite db without primary >key or rowid order? > >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. > > >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? > >we need a way where by we can get the entry as we given in "where" >"in" clause > > >Thanks and Regards > >Deepak > > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
Whoops, your right. I should have tested. I am more used to MySQL and assumed field was more "standard". Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of x Sent: Fri 09 March 2018 13:40 To: SQLite mailing list Subject: [External] Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order? This Message originated outside your organization. Don’t think there is a FIELD function in sqlite Andy (MySQL has one). with cte(ID) as (values (3),(1),(2)) select * from cte inner join Array using (ID); will return them in the required order. That is to say, if you have a table where the records are ordered 3, 1, 2 then you can get the required result. As to whether that’s any use to you depends on how you’re creating the (3, 1, 2) list. From: sqlite-users on behalf of Andy Ling Sent: Friday, March 9, 2018 1:30:25 PM To: 'SQLite mailing list' Subject: Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order? First, you cannot rely on the order of the rows unless you specify it. So it is "just luck" that they are in ID order. To get want you want you must specify an order and something like this will do what you want.. SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2); So you need to put the same IDs in the FIELD as you have in the IN. HTH Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar (D.) Sent: Fri 09 March 2018 13:15 To: sqlite-users@mailinglists.sqlite.org Subject: [External] [sqlite] How to get the entry from sqlite db without primary key or rowid order? This Message originated outside your organization. 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. 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? we need a way where by we can get the entry as we given in "where" "in" clause Thanks and Regards Deepak ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
Don’t think there is a FIELD function in sqlite Andy (MySQL has one). with cte(ID) as (values (3),(1),(2)) select * from cte inner join Array using (ID); will return them in the required order. That is to say, if you have a table where the records are ordered 3, 1, 2 then you can get the required result. As to whether that’s any use to you depends on how you’re creating the (3, 1, 2) list. From: sqlite-users on behalf of Andy Ling Sent: Friday, March 9, 2018 1:30:25 PM To: 'SQLite mailing list' Subject: Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order? First, you cannot rely on the order of the rows unless you specify it. So it is "just luck" that they are in ID order. To get want you want you must specify an order and something like this will do what you want.. SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2); So you need to put the same IDs in the FIELD as you have in the IN. HTH Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar (D.) Sent: Fri 09 March 2018 13:15 To: sqlite-users@mailinglists.sqlite.org Subject: [External] [sqlite] How to get the entry from sqlite db without primary key or rowid order? This Message originated outside your organization. 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. 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? we need a way where by we can get the entry as we given in "where" "in" clause Thanks and Regards Deepak ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?
First, you cannot rely on the order of the rows unless you specify it. So it is "just luck" that they are in ID order. To get want you want you must specify an order and something like this will do what you want.. SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2); So you need to put the same IDs in the FIELD as you have in the IN. HTH Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar (D.) Sent: Fri 09 March 2018 13:15 To: sqlite-users@mailinglists.sqlite.org Subject: [External] [sqlite] How to get the entry from sqlite db without primary key or rowid order? This Message originated outside your organization. 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. 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? we need a way where by we can get the entry as we given in "where" "in" clause Thanks and Regards Deepak ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mimecast.com --- ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to get the entry from sqlite db without primary key or rowid order?
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. 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? we need a way where by we can get the entry as we given in "where" "in" clause Thanks and Regards Deepak ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
