Query returning too many results

Question

SQL query that returns expected 29 results for a.id = 366

    select a.name, c.name, MAX(B.date), MAX(b.renew_date) as MAXDATE
    from boson_course c
    inner join boson_coursedetail b on (c.id = b.course_id)
    inner join boson_coursedetail_attendance d on (d.coursedetail_id = b.id)
    inner join boson_employee a on (a.id = d.employee_id)
    where a.id = 366
    GROUP BY a.name, c.name
    order by MAX(b.renew_date), MAX(b.date) desc;

SQL code below that returns 34 results, multiple results where two different Provides supplied the same course. I know these extra results are because I added e.name to the list to be returned. But all that is needed is the 29 entries with the latest date and Providers names.

    select a.name, c.name, e.name, MAX(B.date), MAX(b.renew_date) as MAXDATE
    from boson_course c
    inner join boson_coursedetail b on (c.id = b.course_id)
    inner join boson_coursedetail_attendance d on (d.coursedetail_id = b.id)
    inner join boson_employee a on (a.id = d.employee_id)
    inner join boson_provider e on b.provider_id = e.id
    where a.id = 366
    GROUP BY a.name, c.name, e.name
    order by MAX(b.renew_date), MAX(b.date) desc;

Can anyone rework this code to return a single DISTINCT Provider name with the MAX(renew_date) for each course.

I'm confused since adding the provider on the id for the provider, assuming boson_provider.id is the primary key, shouldn't have added any new rows. Is there any chance there are two providers with the same id in the boson_provider table? — DerekCate, Mar 25 '15 at 20:13
Do you want the `date` from the row with the `MAX(renew_date)` or `max(date)`, which may be different. As always, your version of Postgres should be in the question. — Erwin Brandstetter, Mar 25 '15 at 20:18

score 1 · Accepted Answer · edited May 23 '17 at 11:56

This returns exactly one row per distinct combination of (a.name, c.name):
The one with the latest renew_date.
Among these, the one with the latest date (may differ from global max(date)!).
Among these, the one with the alphabetically first e.name:

SELECT DISTINCT ON (a.name, c.name)
       a.name AS a_name, c.name AS c_name, e.name AS e_name
     , b.renew_date, b.date
FROM   boson_course       c
JOIN   boson_coursedetail b on c.id = b.course_id
JOIN   boson_coursedetail_attendance d on d.coursedetail_id = b.id
JOIN   boson_employee     a on a.id = d.employee_id
JOIN   boson_provider     e on b.provider_id = e.id
WHERE  a.id = 366
ORDER  BY a.name, c.name
     , b.renew_date DESC NULLS LAST
     , b.date DESC NULLS LAST
     , e.name;

The result is sorted by a_name, c_name first. If you need your original sort order, wrap this in a subquery:

SELECT *
FROM  (<query from above>) sub
ORDER  BY renew_date DESC NULLS LAST
        , date DESC NULLS LAST
        , a_name, c_name, e_name;

Explanation for DISTINCT ON:

Select first row in each GROUP BY group?

Why DESC NULL LAST?

PostgreSQL sort by datetime asc, null first?

Aside: Don't use basic type names like date ad column names. Also, name is hardly ever a good name. As you can see, we have to use aliases to make this query useful. Some general advice on naming conventions:

How to implement a many-to-many relationship in PostgreSQL?

Many thanks Erwin for the update, works like a dream. And thank you for the extra insightful/educational links. — Tommy Gibbons, Mar 26 '15 at 12:10

score 0 · Answer 2 · answered Mar 25 '15 at 19:54

Try using distinct on:

select distinct on (a.name, c.name, e.name), a.name, c.name, e.name,
       B.date, b.renew_date as MAXDATE
from boson_course c
inner join boson_coursedetail b on (c.id = b.course_id)
inner join boson_coursedetail_attendance d on (d.coursedetail_id = b.id)
inner join boson_employee a on (a.id = d.employee_id)
inner join boson_provider e on b.provider_id = e.id
where a.id = 366
ORDER BY a.name, c.name, e.name, B.date desc
order by MAX(b.renew_date), MAX(b.date) desc;

Query returning too many results

2 Answers2