Select one row for each user_id without effecting other columns?

[wright67uk]

Im using the below select statement;

 

SELECT fname, lname, yard, club FROM registration LEFT JOIN snag ON
registration.user_id=snag.user_id group BY yard desc limit 10

 

Which gives me;

 

fname --- lname -------yard ------club -------

 

name 1 - lname 1--------300--------Driver

name 1 - lname 1--------250--------7 Iron

name 3 - lname 3--------200--------Driver

name 1 - lname 1--------190--------4 iron.

anme 2 - lname 2--------190--------4 iron

 

What im after though is

 

name 1 - lname1 ---------300--------Driver

name 3 - lname3 ---------200 -------Driver

name 2 - lname 2 --------190 -------4 iron

 

So only one row per user_id.

 

Ive learnt that using distinct will effect all columns.

 

Is there another way to get my desired results?

[Barand]

So if get one row per player what values are you expecting for club and yards

 

- just any at random

- maximum yards and club used

- minimum and club used

[wright67uk]

Im looking for the maximum yards and the club used

[Jessica]

And when they match like in your example?

[Barand]

do your tables (registration and snag) have player_id to link them?

[wright67uk]

Jessica - im not sure what you are asking? - my last post was in reply to @Barand

 

Im looking to make a descending list of the top ten highest scorers

I would like to show the best score (yard) for each unique user (user_id) but limited to the 10 users with the highest scores (yard).

[Barand]

Im looking to make a descending list of the top ten highest scorers

I would like to show the best score (yard) for each unique user (user_id) but limited to the 10 users with the highest scores (yard).

 

Steady on there Wright67uk, you've just told what it is you are trying to do!

[wright67uk]

Oops sorry! your post came through while I was still writing. I really didnt mean to sound rude there!

(I wasnt to sure if I was rambling and not explaining clearly ... I do that somtimes :-) )

 

And yes, both of the tables have a user_id column.

[Jessica]

I don't think anyone thought you were being rude, just unclear.

 

Barand is great at writing these types of SQL queries, I'm sure he'll be able to help.

[Barand]

try

SELECT reg.fname, reg.lname, snag.yard, snag.club
FROM registration reg
   INNER JOIN snag
    ON registration.user_id=snag.user_id
   INNER JOIN
    (
    SELECT user_id, MAX(yard) as maxyard
    FROM snag
    GROUP BY user_id
    ) as maxyd
    ON snag.user_id = maxyd.user_id AND snag.yard = maxyd.maxyard
ORDER BY snag.yard desc
LIMIT 10