Depends on what he means by "first". If alphabetically Igor's solution works....but I'm not sure why you alphabetize singled query result like that. If you want the first ones inserted into the table try this:
select * from table2 t1where t1.language = 'spanish' or not exists (select 1 from table2 t2 where t2.id = t1.id and (t2.language = 'spanish' or t2.rowid < t1.rowid)); For this data CREATE TABLE table2 (id integer, language text, title text, primary key (id, language)); INSERT INTO "table2" VALUES(1,'spanish','title1'); INSERT INTO "table2" VALUES(2,'english','title2'); INSERT INTO "table2" VALUES(3,'russian','title3'); INSERT INTO "table2" VALUES(3,'english','title3'); INSERT INTO "table2" VALUES(3,'spanish','title3'); INSERT INTO "table2" VALUES(2,'dutch','title2'); INSERT INTO "table2" VALUES(2,'danish','title2'); Igor's produces: 1|spanish|title1 3|spanish|title3 2|danish|title2 The rowid one produces: 1|spanish|title1 2|english|title2 3|spanish|title3 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: [email protected] on behalf of Igor Tandetnik Sent: Tue 11/23/2010 11:28 PM To: [email protected] Subject: EXTERNAL:Re: [sqlite] Multilingual query SQLumee SQLumee <[email protected]> wrote: > Hi all, I am trying to retrieve multilingual information from a table but I > don't know how to do this query. > > I have a table defined as: > CREATE TABLE table2 (id integer, language text, title text, primary key (id, > language)); > > What I want to retrieve are the rows with "spanish" titles or if there is no > spanish title available, select the title in the > first language found. Try this: select * from table2 t1 where t1.language = 'spanish' or not exists ( select 1 from table2 t2 where t2.id = t1.id and (t2.language = 'spanish' or t2.language < t1.language) ); Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
