How to get the entry from sqlite db without primary key or rowid order?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

How to get the entry from sqlite db without primary key or rowid order?

Deepak Hegde
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Andy Ling-2
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:[hidden email]] On Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

curmudgeon
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 <[hidden email]> on behalf of Andy Ling <[hidden email]>
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:[hidden email]] On Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Andy Ling-2
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:[hidden email]] 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 <[hidden email]> on behalf of Andy Ling <[hidden email]>
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:[hidden email]] On Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Keith Medcalf
In reply to this post by Deepak Hegde

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-
>[hidden email]] On Behalf Of Hegde, Deepakakumar
>(D.)
>Sent: Friday, 9 March, 2018 06:15
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

R Smith-2
In reply to this post by Deepak Hegde


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Richard Hipp-3
In reply to this post by Deepak Hegde
On 3/9/18, Hegde, Deepakakumar (D.) <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Jim Callahan
In reply to this post by Deepak Hegde
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

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link>
<#m_-7888910753152976491_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, Mar 9, 2018 at 8:14 AM, Hegde, Deepakakumar (D.) <
[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to get the entry from sqlite db without primary key or rowid order?

Richard Hipp-3
In reply to this post by Deepak Hegde
On 3/9/18, Hegde, Deepakakumar (D.) <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users