On 4 January 2011 08:19, SQLumee SQLumee <sqlu...@hotmail.com> wrote: > > Hi all, I've got a query that give me this result getting the programs which > starts after "00010": > select * from programs where start_date>00010; > CHANNEL | START_DATE | TITLE0 | 00010 | Pocahontas0 | 00011 | > The Lion King0 | 00024 | Back to the Future1 | 00012 | 1_aaaa1 > | 00080 | 1_bbbb2 | 01000 | 2_aaaa2 | 00050 | 2_bbbb2 | 00010 > | 2_ccccc
It's difficult to make sense of this. It looks like there are 3 column titles (CHANNEL, START_DATE, TITLE0), but data for only 2 columns... > > But, what I want to do is get only the first program (ordering by start_date) > for each channel, I meant: > CHANNEL | START_DATE | TITLE0 | 00010 | Pocahontas1 | 00012 | > 1_aaaa2 | 00010 | 2_ccccc > I am playing with distinct, group_by and order_by but I dont get it :( > Does this give some idea? sqlite> PRAGMA foreign_keys=OFF; sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE tst( id integer primary key, channel integer, start_date text, title text ); sqlite> INSERT INTO "tst" VALUES(1,2,'00010','Title1'); sqlite> INSERT INTO "tst" VALUES(2,1,'00012','Title2'); sqlite> INSERT INTO "tst" VALUES(3,1,'00020','Title3'); sqlite> INSERT INTO "tst" VALUES(4,1,'00013','Title4'); sqlite> INSERT INTO "tst" VALUES(5,2,'00013','Title5'); sqlite> INSERT INTO "tst" VALUES(6,2,'00011','Title4'); sqlite> INSERT INTO "tst" VALUES(7,3,'00015','Title6'); sqlite> INSERT INTO "tst" VALUES(8,1,'00014','Title7'); sqlite> INSERT INTO "tst" VALUES(9,2,'00014','Title3'); sqlite> INSERT INTO "tst" VALUES(10,3,'00017','Title1'); sqlite> COMMIT; sqlite> sqlite> -- Get channel and earliest start_date that satisifes the selection criteria sqlite> sqlite> select channel, min( start_date ) from tst where start_date>'00010' group by channel; 1|00012 2|00011 3|00015 sqlite> -- Get title by joining tst with channel & min( start_date ) sqlite> select main.channel, start_date, title from tst main, ( select channel, min( start_date ) date from tst where start_date> '00010' group by channel) chn on chn.channel = main.channel and chn.date=main.start_date; 1|00012|Title2 2|00011|Title4 3|00015|Title6 sqlite> Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
