Distinct Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct Records

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 16:40:39

Hi, I am trying to select distinct records from a "scores" table and inner join to a "users" table. However, when I inner join and order by i don't get distinct records.

can someone help me with this?


SELECT  	 DISTINCT		golferscore.golferid,
										golferscore.ranker as ranker,
										golferscore.stablfordtotal as stableford, 
										Golfers.firstname as firstname, 
										golfers.lastname as lastname

FROM								GolferScore 

INNER JOIN					golfers 

ON									GolferScore.golferID = golfers.golfID

order by							golferScore.ranker desc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 16:46:23

So the output will show you WHY you get more records than expected.
And I would bet: they are DISTINCT.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 16:48:57

Are you trying to select the highest rank/score for each golfer??

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 16:49:56

What I would like to get is the highest score for each player in the database table "golferscore". Lets say John Doe has 5 scores and Jane Doe has 10 scores, I would like to pull only their highest score. Does that make sense?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 16:50:23

quote:
Originally posted by vijayisonly

Are you trying to select the highest rank/score for each golfer??

Yes

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 16:51:02

and also the golfers name from the golfer table

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 16:51:50

Try this

SELECT a.golferid, 
       a.ranker         AS ranker, 
       a.stablfordtotal AS stableford, 
       b.firstname      AS firstname, 
       b.lastname       AS lastname 
FROM   (SELECT ROW_NUMBER() 
                 OVER(PARTITION BY golferID ORDER BY ranker DESC) AS rnk, 
               golferid, 
               ranker,
               stablfordtotal 
        FROM   GolferScore) a 
       INNER JOIN golfers b 
         ON a.golferID = b.golfID 
WHERE  a.rnk = 1

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 16:58:00

I receive an this error:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'stablfordTotal'.

but that is the right column name tho. It works when I take it out

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 17:00:45

maybe it is stablefordTotal

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 17:02:09

If thats the right column name...it shouldn't throw an error...I just copied it from your initial query...

quote:

SELECT  	 DISTINCT		golferscore.golferid,
										golferscore.ranker as ranker,
										golferscore.stablfordtotal as stableford, 
										Golfers.firstname as firstname, 
										golfers.lastname as lastname

FROM								GolferScore 

INNER JOIN					golfers 

ON									GolferScore.golferID = golfers.golfID

order by							golferScore.ranker desc

did this work?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 17:11:05

I had an error in my typing whoops! this works but it is not ordering by desc. what could it be?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 17:13:34

because its not coded for in the query...

SELECT a.golferid, 
       a.ranker         AS ranker, 
       a.stablfordtotal AS stableford, 
       b.firstname      AS firstname, 
       b.lastname       AS lastname 
FROM   (SELECT ROW_NUMBER() 
                 OVER(PARTITION BY golferID ORDER BY ranker DESC) AS rnk, 
               golferid, 
               ranker,
               stablfordtotal 
        FROM   GolferScore) a 
       INNER JOIN golfers b 
         ON a.golferID = b.golfID 
WHERE  a.rnk = 1 
ORDER BY a.ranker DESC

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 17:14:36

Perfect! Thank you so much I have never used sub queries before

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 17:15:06

WELCOME

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2009-08-25 : 17:31:00

Can you explain what you did with this:

SELECT ROW_NUMBER()
OVER(PARTITION BY golferID ORDER BY ranker DESC) AS rnk,

and this:
WHERE a.rnk = 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-25 : 17:46:30

Execute the subquery separately, it will give you an idea.

Read about ROW_NUMBER () here

http://msdn.microsoft.com/en-us/library/ms186734.aspx

WHERE a.rnk = 1 is the condition to select the top score of the golfer.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-25 : 17:47:34

ROW_NUMBER() is numbering the rows starting at value 1 and going on with 2, 3 and so on.
PARTITION BY golferID says: At change on golferID start over with value 1.
ORDER BY ranker DESC says: inside the rows for a golferID start with value 1 for the highest ranker.

Where a.rnk = 1 selects only the highest ranker because this row has rownumber 1.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-26 : 01:00:45

Also read http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources