sql - Getting distinct result without using the DISTINCT clause

Question

I have the following code, I am able to get the result I desire by using DISTINCT but is there a way to get the same result without using DISTINCT?

SELECT S.name, COUNT(DISTINCT R.peopleID) AS numathletes
FROM Sports S
JOIN Results R ON S.ID = R.sportID
WHERE R.result >= S.record
GROUP BY S.name;

as for the tables i'm using:
Table S has ID, name, record
Table R has peopleID, competitionID, sportID, result

标签： sqlpostgresql

Answer 1

Yes, it is possible.

Here is one variant

SELECT name, COUNT(*) AS numathletes
FROM
(
    SELECT S.name, R.peopleID
    FROM 
        Sports S
        JOIN Results R ON S.ID = R.sportID
    WHERE R.result >= S.record
    GROUP BY S.name, R.peopleID
) AS T
GROUP BY name;

It is a more verbose variant which shows clearly how the calculations are done.

You should try and check with your data and hardware which variant is faster. Quite likely that they will perform the same.