Distinct and grouping problem

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

That is because you are grouping on more than one column. The total column will get you additional values / rows in the Grouping section.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply.

So if I take the second grouping out how can I make the information 'line up' correctly?

i.e. have the correct date, handicap, score with the right total. As at the moment they are the wrong values for the Total.

|||That is hard to tell, because you only told that the query "is not working". Depending on your business case, you might want to get the max(total) or not ? That depends on the logic you want to put in the query.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

ok, 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)

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.