How Can I Correctly Do this Limited Group By Query in MySQL?

Question

I have a query that is returning a list of 25 doctors that needs to reflect their uploaded files (cloud_uploads) in a column. The problem is, I do not want to limit the number of files that are listed, but the limit seems to be applying to the files as well. Other similar questions on SO that I've seen have the opposite problem - they want to limit the files without limiting the total rows returned. I have doctors with more than 25 uploaded files, but the list is only showing the first 25 files rather than all of them.

Because this is a programmatically generated query, I can't separate it into multiple queries. It needs to be one. So, if the answer is that it can't be done, I can accept that.

Here is the current query:

SELECT SQL_CALC_FOUND_ROWS records.instance_id,
        instances.patient_id,
        TRIM(form_1_data.field_2) AS field_0,
        TRIM(form_1_data.field_1) AS field_1,
        TRIM(form_1_data.field_87) AS field_2,
        GROUP_CONCAT(DISTINCT CONCAT (
                        extensible_8_data.field_953,
                        '|||',
                        cloud_uploads.NAME
                        ) SEPARATOR '<br />') AS field_3
FROM form_6_data AS records
LEFT JOIN instances
        ON instances.id = records.instance_id
LEFT JOIN extensible_8_data
        ON records.instance_id = extensible_8_data.instance_id
LEFT JOIN cloud_uploads
        ON extensible_8_data.field_953 = cloud_uploads.id
LEFT JOIN form_1_data
        ON records.field_89 = form_1_data.instance_id
WHERE instances.form_id = 6
        AND instances.active = 1
        AND (
                cloud_uploads.active = 1
                OR cloud_uploads.active IS NULL
                )
GROUP BY records.instance_id
ORDER BY records.instance_id DESC LIMIT 0, 25

What is the exact criteria of the "needs to upload the files" bit? the limiting the number of doctors bit should be easier once thats established. — BugFinder, Oct 08 '12 at 14:40
Your question is phrased in terms of doctors and files. Your fields don't seem to exactly match this terminology. Can you be a bit clearer about what you want, in a language consistent with the query? — Gordon Linoff, Oct 08 '12 at 14:42
Sorry :) The records alias is for doctors and cloud_uploads is the files. These are dynamically generated queries that work on user-created forms and tables. extensible_8_data.field_953 contains the cloud_uploads id that is used to join the doctor data to the cloud_uploads. — Amy Anuszewski, Oct 08 '12 at 14:46

score 1 · Accepted Answer · answered Oct 08 '12 at 16:17

Join a subquery that selects the 25 doctors of interest with their files:

SELECT SQL_CALC_FOUND_ROWS records.instance_id,
        instances.patient_id,
        TRIM(form_1_data.field_2)  AS field_0,
        TRIM(form_1_data.field_1)  AS field_1,
        TRIM(form_1_data.field_87) AS field_2,
        GROUP_CONCAT(DISTINCT CONCAT (
                        extensible_8_data.field_953,
                        '|||',
                        cloud_uploads.NAME
                        ) SEPARATOR '<br />') AS field_3
FROM (SELECT * FROM form_6_data ORDER BY instance_id DESC LIMIT 25) AS records
LEFT JOIN instances
        ON instances.id = records.instance_id
LEFT JOIN extensible_8_data
        ON records.instance_id = extensible_8_data.instance_id
LEFT JOIN cloud_uploads
        ON extensible_8_data.field_953 = cloud_uploads.id
LEFT JOIN form_1_data
        ON records.field_89 = form_1_data.instance_id
WHERE instances.form_id = 6
        AND instances.active = 1
        AND (
                cloud_uploads.active = 1
                OR cloud_uploads.active IS NULL
                )
GROUP BY records.instance_id
ORDER BY records.instance_id

How Can I Correctly Do this Limited Group By Query in MySQL?

1 Answers1