SQL statement - Distinct and groupinf problem

[colouredFunk]

can someone look at the statement for me, it's not giving me distinct names,

SELECT DISTINCT TOP 10 leaderboard.name, Last(leaderboard.date_cr) AS date_cr, Last(leaderboard.handicap) AS handicap, Max(leaderboard.total) AS total, Last(leaderboard.cpsa) AS cpsa, Last(leaderboard.rbss) AS rbss, Max(leaderboard.visible) AS visible, Last(leaderboard.score) AS score
FROM leaderboard
WHERE (((leaderboard.visible)=True) AND ((leaderboard.rbss)=True))
GROUP BY leaderboard.name, leaderboard.total
ORDER BY Max(leaderboard.total) DESC , Last(leaderboard.date_cr);

if I take the second grouping off, I get distinct names, but the information isn't correct. i.e. the date, handicap, score, is from different record than the total field.

any help much appreciated.

thanks

 

[JarlH]

When GROUP BY, all the columns in the SELECT list must either be specified in the GROUP BY clause, or be arguments to set functions (MAX/MIN/SUM etc). A column used in the GROUP BY clause may not be used in a set function.

What happens if you remove leaderboard.total from the GROUP BY?

(BTW, you may also remove the DISTINCT keyword, because it makes no sense when GROUP BY.)

 

[colouredFunk]

hey thanks for your reply

I've taken off DISTINCT (thanks for that, it doesn't effect the results taking it off at all) and removed leaderboard.total from the GROUP BY - but now the information doesn't display correct. i.e. the date, handicap, score, is from different record than the total field

SELECT TOP 10 leaderboard.name, Last(leaderboard.date_cr) AS date_cr, Last(leaderboard.handicap) AS handicap, Max(leaderboard.total) AS total, Last(leaderboard.cpsa) AS cpsa, Last(leaderboard.rbss) AS rbss, Max(leaderboard.visible) AS visible, Last(leaderboard.score) AS score
FROM leaderboard
WHERE (((leaderboard.visible)=True) AND ((leaderboard.rbss)=True))
GROUP BY leaderboard.name
ORDER BY Max(leaderboard.total) DESC , Last(leaderboard.date_cr);

 

[r937]

A column used in the GROUP BY clause may not be used in a set function"

no, that isn't accurate, see this example --

select X, sum(X) from T
group by X

even this is legal (although possibly not useful) --

select sum(X) from T
group by X


r937.com | rudy.ca

 

[JarlH]

"A column used in the GROUP BY clause may not be used in a set function"

no, that isn't accurate, see this example --

Sorry about that. Thanks for the correction!

 

[colouredFunk]

I'm having real trouble with this query, can't seem to get it right.

These are the rules

1. If somebody has played more than once, only the highest score as calculated in one above should be displayed
2. the results should display correct details by total score in descending order
3. In the case where there are two or more equal total scores by different competitors, these should be sorted by date in ascending order (e, i - January 31 before Feb 12)

 

[PHV]

A starting point:
SELECT L.name, L.date_cr, L.handicap, L.total, L.cpsa, L.rbss, L.visible, L.score
FROM leaderboard L INNER JOIN (
SELECT name, MAX(total) AS maxTotal FROM leaderboard WHERE visible AND rbss GROUP BY name
) M ON L.name = M.name AND L.total = M.maxTotal
WHERE L.visible AND L.rbss
ORDER BY L.total DESC, L.date_cr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[colouredFunk]

Hey thanks PHV that's almost perfect!!!

Is it possible to make a slight change to it -

It still shows the duplicate records - but only when all the information is the same.

I really really appreciate your help