Selecting specific rows - 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)

Selecting specific rows

Author

Topic

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 06:44:49

Imagine I have a table that holds records for each round of golf a player has played.

This table has fields such as CourseID, Player and Hits.

Clearly Players can play multiple courses and also the same course multiple times.

I want to select the rows that contain a players lowest amount of hits for a certain course.

My SQL statement worked for 4 golfers playing a single course but as soon as I added golfers for another course it broke!

My SQL statement at the moment is:

SELECT R.* FROM Rounds R
WHERE (R.Total = (SELECT MIN(Rounds.Total)
FROM Rounds
WHERE Rounds.Golfer_ID = R.Golfer_ID )
)
AND (R.Course_ID = 1)

It's like this page showing the distinct function http://www.w3schools.com/SQL/sql_distinct.asp
It's great but if you want to see the P_Id as well your knacked!

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 07:10:24

Try this

declare @Rounds table (Course_ID int,Golfer_ID varchar(100), Total int)

insert into @Rounds values (1,'a',1)
insert into @Rounds values (1,'b',2)
insert into @Rounds values (1,'c',3)
insert into @Rounds values (1,'a',2)
insert into @Rounds values (1,'b',3)
insert into @Rounds values (1,'c',5)
insert into @Rounds values (2,'a',3)
insert into @Rounds values (2,'b',1)
insert into @Rounds values (3,'c',5)
insert into @Rounds values (3,'a',3)
insert into @Rounds values (3,'b',1)

SELECT COURSE_ID,Golfer_ID, TOTAL
from
(
SELECT COURSE_ID,Golfer_ID,MIN(TOTAL) AS TOTAL,
DENSE_RANK() OVER (ORDER BY MIN(TOTAL)) AS Rownum
FROM @Rounds a
WHERE Course_ID = 1
GROUP BY COURSE_ID , Golfer_ID ) as a
WHERE Rownum = 1

Karthik

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 07:11:08

Maybe:

Select CourseID, Player and Hits
FROM
(Select Dense_rank() over (partition by CourseID order by Hits,Player)as seq,* from Table)Z
Where Z.seq =1

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 07:23:28

This is where I look like an idiot...

Dense_rank is not a recognized function name...which means I'm not using the SQL 2005 server but an older version!

Sorry for wasting your time there...but I guess I thought there would be an easier solution and as this was a fairly active forum...

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 07:32:40

No problem for SQL server 2000 use this one.

SELECT COURSE_ID,Golfer_ID,MIN(TOTAL) AS TOTAL
FROM @Rounds a
WHERE Course_ID = 1
GROUP BY COURSE_ID , Golfer_ID
having min(total) = (select min(total) from @rounds )

Karthik

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 09:08:33

Nope didn't work. The query only returns one row which is ofcourse the lowest for that person!

If we look at the database:

declare @Rounds table (Course_ID int,Golfer_ID varchar(100), Total int)

insert into @Rounds values (1,'a',1)
insert into @Rounds values (1,'b',2)
insert into @Rounds values (1,'c',3)
insert into @Rounds values (1,'a',2)
insert into @Rounds values (1,'b',3)
insert into @Rounds values (1,'c',5)
insert into @Rounds values (2,'a',3)
insert into @Rounds values (2,'b',1)
insert into @Rounds values (3,'c',5)
insert into @Rounds values (3,'a',3)
insert into @Rounds values (3,'b',1)

If I use your solution I get one record:

SELECT COURSE_ID,Golfer_ID,MIN(TOTAL) AS TOTAL
FROM @Rounds a
WHERE Course_ID = 1
GROUP BY COURSE_ID , Golfer_ID
having min(total) = (select min(total) from @Rounds )

Course Golfer Total
1 a 1

If I use my one I get two records:

SELECT R.* FROM @Rounds R
WHERE (R.Total = (SELECT MIN(Total) FROM @Rounds WHERE Golfer_ID = R.Golfer_ID ))
AND (R.Course_ID = 1)

Course Golfer Total
1 a 1
1 c 3

Of course what I am hoping to get is the lowest total for each player of course 1:

Course Golfer Total
1 a 1
1 b 2
1 c 3

Your solution is clearly returning the lowest for that course only and I have no idea what mine is doing. I did think it was returning only those that had played one course but that doesn't seem to be the case using your data!

Thanks for looking into it though.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 09:16:48

try this one.

SELECT COURSE_ID,Golfer_ID,MIN(TOTAL) AS TOTAL
FROM @Rounds a
WHERE Course_ID = 1
GROUP BY COURSE_ID , Golfer_ID

Karthik

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 09:23:06

[code]SELECT COURSE_ID,GOLF_ID,TOTAL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY COURSEID,GOLF_ID ORDER BY TOTAL)AS SEQ,* FROM TABLE
WHERE COURSE_ID = 1)Z
WHERE Z.SEQ =1[/code]

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 09:39:07

I've opted for Karthik's solution which works wonderfully and hmm is a lot more simpler than sodeeps which makes me realise that my knowledge of SQL is still remarkably small even though I use it every day!

SELECT COURSE_ID,Golfer_ID,MIN(TOTAL) AS TOTAL
FROM @Rounds a
WHERE Course_ID = 1
GROUP BY COURSE_ID , Golfer_ID

Karthik's example tells me that I just need to practice with the Group by clause which I have to admit I hardly ever use if at all!

Thanks for your help though you two.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 09:45:57

You are welcome. Feel free to ask questions.

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources