On Wed, Mar 9, 2011 at 3:53 AM, eric wong <ewl...@gmail.com> wrote: > Here is the situation. I have one database table "TB_Patient" with the > columns below: > > PATIENT_ID > PATIENT_GROUP > FIRSTNAME > LASTNAME > ADDRESS > > I want to get PATIENT_ID randomly from certain PATIENT_GROUP. > > There are 5 PATIENT_GROUP only with total row in the table is about > 10000 (10K). > > The number of data per PATIENT_GROUP is not the same. Certain > PATIENT_GROUP has less data than the others. > > Do you know the FASTEST possible SQL query for this task? > > When there is NO PATIENT_GROUP, I could come up with this query: > > SELECT PATIENT_ID FROM TB_Patient WHERE rowid >= (ABS(RANDOM()) % > (SELECT MAX(rowid) FROM TB_Patient)) LIMIT 1 > > But now, I must get the data from certain PATIENT_GROUP. > > What's your best approach to solve this? The objective is the fastest > possible query. >
How about this: SELECT patient_id FROM TB_Patient WHERE patient_group=$group ORDER BY random() LIMIT 1; > > Thanks. > > -- > Regards, > > E. Lung > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
