Getting random data from grouped data in one table

Here is the situation. I have one database table 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? By the way, I am using SQLite3.

When there is no PATIENT_GROUP, I could come up with this query:

SELECT PATIENT_ID 开发者_StackOverflowFROM TB_Patient 
WHERE rowid >= (ABS(RANDOM()) % (SELECT MAX(rowid) FROM TB_Patient)) LIMIT 1

What's your best approach to solve this? The objective is the fastest possible query.

Thanks.

---

try using the following query with different <group_id> according to the patient_group you are running on:

SELECT PATIENT_ID 
FROM TB_Patient 
WHERE PATIENT_GROUP = <group_id> 
ORDER BY RANDOM() 
LIMIT 1

I am not sure it is faster but it should be more easy to work with.

Good Luck!