Get a specific sequence of rows...

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Get a specific sequence of rows...

Fredrik Karlsson
Hi,

I have a list of id:s stored in a field. I would now like to get some
information from a table by these id:s, but exactly in this order. So,
if I have a table

1 One
2 Two
3 Three

and the sequence "3,1,2" stored somewhere, how do I get a neat list like

Three
One
Two

?
I I can see a solution where I split the string outside of sqlite and
then construct a query consisting of lots of UNIONs, like (not a full
example, just an illustration)

(select name from mytab where id = 3)
UNION ALL
(select name from mytab where id = 1)
UNION ALL
(select name from mytab where id = 2)
...
...

but maybe there is a better option somewhere?
I would be thankful for any help I can get.

/Fredrik

--
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Get a specific sequence of rows...

David Bicking-2
On Fri, 2010-03-26 at 10:00 +0100, Fredrik Karlsson wrote:

> Hi,
>
> I have a list of id:s stored in a field. I would now like to get some
> information from a table by these id:s, but exactly in this order. So,
> if I have a table
>
> 1 One
> 2 Two
> 3 Three
>
> and the sequence "3,1,2" stored somewhere, how do I get a neat list like
>
> Three
> One
> Two
>
> ?
> I I can see a solution where I split the string outside of sqlite and
> then construct a query consisting of lots of UNIONs, like (not a full
> example, just an illustration)
>
> (select name from mytab where id = 3)
> UNION ALL
> (select name from mytab where id = 1)
> UNION ALL
> (select name from mytab where id = 2)
> ...
> ...
>
> but maybe there is a better option somewhere?
> I would be thankful for any help I can get.
>
> /Fredrik
>

I'd create a second table
CREATE TABLE sequence (seq integer primary key, id);

Then insert your desired sequence in to that table
seq | id
1   | 3
2   | 1
3   | 2

Select * From datatable, sequence where datatable.id = sequence.id order
by sequence.seq;

David

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

Re: Get a specific sequence of rows...

Simon Slavin-3
In reply to this post by Fredrik Karlsson

On 26 Mar 2010, at 9:00am, Fredrik Karlsson wrote:

> I have a list of id:s stored in a field. I would now like to get some
> information from a table by these id:s, but exactly in this order. So,
> if I have a table
>
> 1 One
> 2 Two
> 3 Three
>
> and the sequence "3,1,2" stored somewhere, how do I get a neat list like
>
> Three
> One
> Two

I can't find a function which does what you want.  The SQL-like solution to it is to have your '3,1,2' written to a table as three records, then use a JOIN or a sub-SELECT to make SQL return the right records in the right order.

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

Re: Get a specific sequence of rows...

Fredrik Karlsson
On Fri, Mar 26, 2010 at 12:22 PM, Simon Slavin <[hidden email]> wrote:

>
> On 26 Mar 2010, at 9:00am, Fredrik Karlsson wrote:
>
>> I have a list of id:s stored in a field. I would now like to get some
>> information from a table by these id:s, but exactly in this order. So,
>> if I have a table
>>
>> 1 One
>> 2 Two
>> 3 Three
>>
>> and the sequence "3,1,2" stored somewhere, how do I get a neat list like
>>
>> Three
>> One
>> Two
>
> I can't find a function which does what you want.  The SQL-like solution to it is to have your '3,1,2' written to a table as three records, then use a JOIN or a sub-SELECT to make SQL return the right records in the right order.
>
> Simon.

Hi,

This is of course another solution. I guess, coming from R, I was
looking for a substitute for th %in% operator (or the MySQL IN
operator as it turns out, now that I've Googled this some more) but a
temp table would also do the trick I guess.

Thank you!

/Fredrik



--
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Get a specific sequence of rows...

Igor Tandetnik
Fredrik Karlsson wrote:
> This is of course another solution. I guess, coming from R, I was
> looking for a substitute for th %in% operator (or the MySQL IN
> operator as it turns out, now that I've Googled this some more) but a
> temp table would also do the trick I guess.

Well, you can generate a statement of the form

select name from mytab where id in (3, 1, 2);

I don't believe either SQLite or MySQL guarantees that the rows will always come out in the order in which IDs are listed in the IN clause. But I won't be surprised if they do happen to come out this way, as an implementation detail. I'm not sure I'd be comfortable relying on such behavior though.
--
Igor Tandetnik

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

Re: Get a specific sequence of rows...

Gabor Grothendieck
In reply to this post by Fredrik Karlsson
On Fri, Mar 26, 2010 at 5:00 AM, Fredrik Karlsson <[hidden email]> wrote:

> Hi,
>
> I have a list of id:s stored in a field. I would now like to get some
> information from a table by these id:s, but exactly in this order. So,
> if I have a table
>
> 1 One
> 2 Two
> 3 Three
>
> and the sequence "3,1,2" stored somewhere, how do I get a neat list like
>
> Three
> One
> Two
>

Try this:

select *, 1 * (name = "Three") + 2 * (name = "One") + 3 * (name =
"Two") sorter from mytab where sorter > 0 order by sorter
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Get a specific sequence of rows...

Fredrik Karlsson
In reply to this post by Igor Tandetnik
On Fri, Mar 26, 2010 at 1:25 PM, Igor Tandetnik <[hidden email]> wrote:

> Fredrik Karlsson wrote:
>> This is of course another solution. I guess, coming from R, I was
>> looking for a substitute for th %in% operator (or the MySQL IN
>> operator as it turns out, now that I've Googled this some more) but a
>> temp table would also do the trick I guess.
>
> Well, you can generate a statement of the form
>
> select name from mytab where id in (3, 1, 2);
>
> I don't believe either SQLite or MySQL guarantees that the rows will always come out in the order in which IDs are listed in the IN clause. But I won't be surprised if they do happen to come out this way, as an implementation detail. I'm not sure I'd be comfortable relying on such behavior though.
> --
> Igor Tandetnik

Oh, ok. I did not think of that. Indeed, this is the behaviour of R
%in% too, so I should really have thought of this.
Since this is a calculated path in a directed graph, order is
important, and so I will go with a temporary (in memory?) table.

Thank you for your great help!

/Fredrik

--
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users