Q
Problem solve Get help with specific problems with your technologies, process and projects.

Showing one line per patient per day with GROUP BY

How can I limit this output:

PATIENT_ID SERVICE_DATE PROVIDER_ID PROVIDER_SPECIALTY
---------- ------------ ----------- ------------------
         1 01/01/2003   M                            1
         1 01/01/2003   Y                            2
         2 01/01/2003   M                            1
         2 01/01/2003   R                            3
         3 01/01/2003   Y                            2
to show no more than one line per patient per day? provider_specialty is used to pick which provider to show. (I want the provider with the lowest number, but I don't really need to see what that number is.) This is what I'd like to see:
PATIENT_ID SERVICE_DATE PROVIDER_ID
---------- ------------ -----------
         1 01/01/2003   M
         2 01/01/2003   M
         3 01/01/2003   Y

I made some assumptions about your table structure and your first SELECT statement since you didn't provide them.

If we did want to see the lowest provider_specialty for each patient-day and not the provider_id, this would appear more clearly as a GROUP BY problem:

SELECT    patient_id,
          TO_CHAR (service_date, 'MM/DD/YYYY')
              AS service_date,
          MIN (provider_specialty)
FROM      service  s    JOIN
          provider p    USING (provider_id)
GROUP BY  service_date,
          patient_id
ORDER BY  service_date,
          patient_id;

PATIENT_ID SERVICE_DATE MIN(PROVIDER_SPECIALTY)
---------- ------------ -----------------------
         1 01/01/2003                         1
         2 01/01/2003                         1
         3 01/01/2003                         2
The result set above is basically what we want: all we need to do now is convert the last column of this result set to provider_id. That's a simple JOIN involving these two "tables":
  1. the result set shown above
  2. the provider table
Don't get distracted by the fact that the first "table" is itself a JOIN, or that the provider table is one of its components. All we need to do is a two-table OUTER JOIN.
SELECT    patient_id,
          TO_CHAR (service_date, 'MM/DD/YYYY')
              AS service_date,
          provider_id
FROM      (
              SELECT    patient_id,
                        service_date,
                        MIN (provider_specialty)
                            AS provider_specialty
              FROM      service  s    JOIN
                        provider p    USING (provider_id)
              GROUP BY  service_date,
                        patient_id
          )         JOIN
          provider  USING (provider_specialty)
ORDER BY  service_date,
          patient_id;

This answer is continued...


Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close