CODEWITHSUNDEEP
sqliteselect
Phi
Phi

Reputation: 814

select a subset of a table in the order of another column

I got 2 tables like this (streamlined example):

create table T (i integer, a text);
insert into T values (3,'ww');
insert into T values (1,'ee');
insert into T values (5,'nn');
insert into T values (2,'kk');
insert into T values (4,'ss');

create table U (i integer);
insert into U values (2);
insert into U values (3);
insert into U values (5);
insert into U values (1);

select rowid, i from U;
rowid  i
----- --
    1  2
    2  3
    3  5
    4  1

select * from T where i in (select i from U order by rowid);
    i                a
----- ----------------
    3               ww
    5               nn
    2               kk  
    1               ee

I need the i collected in the in(...) in the same order as the inner select, something like:

select * from T where i inUnsorted (select i from U order by rowid);
    i                a
----- ----------------
    2               kk          
    3               ww
    5               nn
    1               ee

Is that possible ?

Upvotes: 1

Views: 624

Answers (3)

CL.
CL.

Reputation: 180070

In SQL, a query is unsorted unless you are using ORDER BY, so this clause is required on the outer query.

Anyway, you can simply look up the value with a correlated subquery:

SELECT *
FROM T
WHERE i IN (SELECT i
            FROM U)
ORDER BY (SELECT rowid
          FROM U
          WHERE U.i = T.i);

Upvotes: 1

Philip Rollins
Philip Rollins

Reputation: 1291

I'm kinda confused by the question. But if I guessed it right this should be what you're wanting?

select T.*, U.i as ui from T inner join U on T.i = U.i;

Upvotes: 0

Phi
Phi

Reputation: 814

Something along this lines, but simplerm i.e removing the temp table

create temp table T2  as select i,0 as f from T;
update T2 set f=(select rowid from U where U.i=T2.i);
select * from T2;
    i                f
----- ----------------
    3                2
    1                4
    5                3
    2                1
    4

select T.i,a from T join T2 on T.i=T2.i where T2.f order by T2.f;
    i                a
----- ----------------
    2               kk
    3               ww
    5               nn
    1               ee

Upvotes: 0

Related Questions