Re: [sqlite] Sqlite query to get the offset of an entry in the list

[Balaji Ramanathan]

SELECT COUNT(*) FROM TABLE WHERE NAME <= (SELECT NAME FROM TABLE WHERE ID =
3 ORDER BY NAME ASC)

Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

[x]

How about



SELECT ID,NAME,

(SELECT COUNT(*) FROM TABLE WHERE NAME<=(SELECT NAME FROM TABLE WHERE ID=d.ID)) 
as Position

FROM TABLE d

ORDER BY ID;



sqlite> create table t(ID,name text);

sqlite> insert into t values (1,'AAA'),(2,'ZZZ'),(3,'BBB'),(4,'WWW'),(5,'CCC');

sqlite> select ID,name,(select count(*) from t where name<=(select name from t 
where ID=d.ID)) as Posn from t d

order by ID;

1|AAA|1

2|ZZZ|5

3|BBB|2

4|WWW|4

5|CCC|3

sqlite>




From: sqlite-users  on behalf of 
Hegde, Deepakakumar (D.) 
Sent: Friday, April 27, 2018 3:51:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Sqlite query to get the offset of an entry in the list.

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


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] Sqlite query to get the offset of an entry in the list.

[Keith Medcalf]

Again, requiring that both "id" and "name" are candidate keys.  In which case, 
since there has to be unique indexes to enforce that, one might use the more 
straightforward:

select count(*) from table where name <= (select name from table where id=?) 
order by name;


---
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 x
>Sent: Friday, 27 April, 2018 09:43
>To: SQLite mailing list
>Subject: Re: [sqlite] Sqlite query to get the offset of an entry in
>the list.
>
>SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE
>WHERE ID = 3)
>
>
>
>(I think)
>
>
>
>
>From: sqlite-users  on
>behalf of Hegde, Deepakakumar (D.) 
>Sent: Friday, April 27, 2018 3:51:27 PM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite query to get the offset of an entry in the
>list.
>
>Hi All,
>
>
>We have a requirement where in offset of the primary key ID is needed
>as per the sorted list.
>
>
>Table:
>
>
>ID   NAME
>
>1  AAA
>
>2  ZZZ
>
>3  BBB
>
>4  WWW
>
>5  CCC
>
>
>Now need to get the offset of the ID 3 in the sorted list of the
>NAME.
>
>
>SELECT * FROM TABLE ORDER BY NAME ASC
>
>
>1   AAA
>
>3   BBB
>
>5   CCC
>
>4   WWW
>
>2   ZZZ
>
>
>So position of ID 3 as per the sorted list of the NAME is 2.
>
>
>currently we are getting the entry with the select statement and by
>comparing the ID externally we are getting the offset.
>
>
>Is there any optimal way to get this information directly with one
>single query?
>
>
>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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

[Keith Medcalf]

The constraint, obviously, being that "id" and "name" are each candidate keys 
...

---
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 Simon Davies
>Sent: Friday, 27 April, 2018 09:35
>To: SQLite mailing list
>Subject: Re: [sqlite] Sqlite query to get the offset of an entry in
>the list.
>
>On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.)
> wrote:
>> Hi All,
>>
>> We have a requirement where in offset of the primary key ID is
>needed as per the sorted list.
>.
>.
>.
>> 1   AAA
>> 3   BBB
>> 5   CCC
>> 4   WWW
>> 2   ZZZ
>>
>> So position of ID 3 as per the sorted list of the NAME is 2.
>
>sqlite> create table t( id integer primary key, data text );
>sqlite> insert into t( data ) values('aaa'), ('zzz'), ('bbb'),
>('www'), ('ccc');
>sqlite>
>sqlite> select count(*)+1 from t where data<(select data from t where
>id=3);
>2
>
>> Thanks and Regards
>> Deepak
>
>Rgds,
>Simon
>___
>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] Sqlite query to get the offset of an entry in the list.

[Keith Medcalf]

create table data (id integer primary key, name text);
insert into data (name) values ('AAA'), ('ZZZ'), ('BBB'), ('WWW'), ('CCC');

select * from data;
1|AAA
2|ZZZ
3|BBB
4|WWW
5|CCC

select * from data order by name;
1|AAA
3|BBB
5|CCC
4|WWW
2|ZZZ

create table temp.ranked as select * from data order by name;

select rowid, * from temp.ranked;
1|1|AAA
2|3|BBB
3|5|CCC
4|4|WWW
5|2|ZZZ

select rowid from temp.ranked where id = 3;
2

drop table temp.ranked;


There is likely a way to do this using a recursive CTE without a temp table, 
however, I cannot do that off the top of my mind immediately as there are too 
many possible constraints.  Someone else may have already thought about how to 
do that.  There are lots of solutions with various "constraints" and 
"assumptions" about the data though. This one happens to not require any such 
assumptions or constraints ... If you, for example, constrained the name column 
to be unique, then there exists a much simpler solution.

Whether or not the sequence:

begin immediate;
drop table if exists temp.ranked;
create table temp.ranked as select * from data order by name;
select rowid from temp.ranked where id = 3;
drop table if exists temp.ranked;
rollback;

constitutes a "single sql statement" depends on how you are interfacing with 
SQLite.  For me, it is a single statement returning a single row.  YMMV.


However, my question would be why you need to know the offset of the ID in the 
sorted set of results as that seems like a "navigational" problem rather than a 
"relational" problem?  Are the "requirements" perhaps geared to file or 
hierachical database rather than a relational database?  (It is quite common 
for "navigational" problems to be stated in requirements for "relational" 
implementations.  This is why over the years there have been many functions 
added to the "monster" relational engines -- because it is easier to add a 
"MakeCoffee()" function than it is to argue that a relational database should 
not have a "MakeCoffee" function.  The epitome of this is of course Oracle, 
which has a function for everything you could ever possibly want to do.)


---
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, 27 April, 2018 08:51
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Sqlite query to get the offset of an entry in the
>list.
>
>Hi All,
>
>
>We have a requirement where in offset of the primary key ID is needed
>as per the sorted list.
>
>
>Table:
>
>
>ID   NAME
>
>1  AAA
>
>2  ZZZ
>
>3  BBB
>
>4  WWW
>
>5  CCC
>
>
>Now need to get the offset of the ID 3 in the sorted list of the
>NAME.
>
>
>SELECT * FROM TABLE ORDER BY NAME ASC
>
>
>1   AAA
>
>3   BBB
>
>5   CCC
>
>4   WWW
>
>2   ZZZ
>
>
>So position of ID 3 as per the sorted list of the NAME is 2.
>
>
>currently we are getting the entry with the select statement and by
>comparing the ID externally we are getting the offset.
>
>
>Is there any optimal way to get this information directly with one
>single query?
>
>
>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] Sqlite query to get the offset of an entry in the list.

[x]

SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE WHERE ID = 3)



(I think)




From: sqlite-users  on behalf of 
Hegde, Deepakakumar (D.) 
Sent: Friday, April 27, 2018 3:51:27 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Sqlite query to get the offset of an entry in the list.

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


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] Sqlite query to get the offset of an entry in the list.

[Simon Davies]

On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.)
 wrote:
> Hi All,
>
> We have a requirement where in offset of the primary key ID is needed as per 
> the sorted list.
.
.
.
> 1   AAA
> 3   BBB
> 5   CCC
> 4   WWW
> 2   ZZZ
>
> So position of ID 3 as per the sorted list of the NAME is 2.

sqlite> create table t( id integer primary key, data text );
sqlite> insert into t( data ) values('aaa'), ('zzz'), ('bbb'), ('www'), ('ccc');
sqlite>
sqlite> select count(*)+1 from t where data<(select data from t where id=3);
2

> Thanks and Regards
> Deepak

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite query to get the offset of an entry in the list.

[Hegde, Deepakakumar (D.)]

Hi All,


We have a requirement where in offset of the primary key ID is needed as per 
the sorted list.


Table:


ID   NAME

1  AAA

2  ZZZ

3  BBB

4  WWW

5  CCC


Now need to get the offset of the ID 3 in the sorted list of the NAME.


SELECT * FROM TABLE ORDER BY NAME ASC


1   AAA

3   BBB

5   CCC

4   WWW

2   ZZZ


So position of ID 3 as per the sorted list of the NAME is 2.


currently we are getting the entry with the select statement and by comparing 
the ID externally we are getting the offset.


Is there any optimal way to get this information directly with one single query?


Thanks and Regards

Deepak

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users