calling the experts - a real heavy one!

[jimmy hill]

here is my query:

--------------------------------------------------------------


<cfquery name="get_vote_chart_2" datasource="xxx" maxrows="30">

SELECT products.the_item_id, products.the_user_id, products.title, products.filename_1, products.filename_2, users.name,
Count(the_chart_vote.chartvote_ID) AS Tha_vote_Count,
max(the_chart_vote.grand_vote_total) AS grandeee,
Max(the_chart_vote.dateposted) As Lastvote,
Min(the_chart_vote.pos) As topper,
Max(the_chart_vote.pos_last) As last_position

FROM products products, the_chart_vote the_chart_vote, users users

where products.the_item_id = the_chart_vote.tha_product_id AND products.the_user_id = USERS.the_user_id AND products.group_id = #the_group# AND the_chart_vote.dateposted > (Now()-14)


GROUP BY products.title, products.the_item_id, products.the_user_id, products.filename_1, products.filename_2, users.name

ORDER BY Count(the_chart_vote.Tha_vote_count) DESC

</cfquery>


------------------------------------------------

it works fine - returning a 30-position chart of votes for products in order of max votes cast per product ID

However, i want to modify the query so it only returns ONE product in the chart by any given USER-ID, so that the chart shows votes for products but only one product owner's product can appear in any given chart

so i need a sort of - DISTICTROW products.the_user_id - to be working

any know how to modify this please. It's foxed me.



cheers

 

[aleb]

You can create another query with
Select distinct yourtable.user_id
Then join two queries in summation query grouping all the records from 1st query with Total: first

 

[Len Boorman]

Can you not simply add a criteria of whichever User ID you wish to be used ?...

L