Finding error (Maximum 2 from each category)

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)

Finding error (Maximum 2 from each category)

Author

Topic

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 11:42:01

I have a bowling database. This database contain 3 tables. The following are the table names and their columns

Teams -> TeamID(PK), TeamName
Bowlers -> BowlerID(PK), BowlerFirstName, BowlerLastName, TeamID(FK)
Bowler_Scores -> MatchID, BowlerID(FK), RawScores

I want a SQL query so to display the details of bowlers from each team having the top 2 raw scores i.e Highest two raw scores from each team. I have wrote this SQL query but am unable to get the corect result. Kindly edit this query and point out the mistake.

SELECT Bowlers.BowlerID,Bowlers.BowlerFirstName,Bowlers.BowlerLastName,Bowlers.TeamID,Teams.TeamName,Bowler_Scores.RawScore FROM (Bowlers LEFT
OUTER JOIN Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) RIGHT OUTER JOIN Teams ON Bowlers.TeamID = Teams.TeamID WHERE Bowlers.BowlerID IN
(SELECT TOP 2 BC.BowlerID FROM (SELECT B.BowlerID, BS.RawScore,B.TeamID FROM Bowler_Scores AS BS INNER JOIN Bowlers AS B ON B.BowlerID = BS.BowlerID ORDER BY BS.RawScore DESC) AS BC WHERE Teams.TeamID = BC.TeamID);

Thanks.
Anubhav

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 12:19:18

Please post some sample data & the o/p expected.

PBUH

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 13:47:54

Operation expected

TeamID TeamName BowlerID BowlerName RawScore
1 A 34 ABC 193
1 A 22 AMD 190
2 B 12 MDI 198
2 B 12 MDI 196
3 C 31 RDX 170
3 C 08 MOS 168

continued...

Here Teams are A,B,C . And each team displays the corresponding bowler with the top 2 highest scores. Please note in Team B, the top 2 scores of 198 and 196 are performed by the same player MDI. So his name appears twice. Listing is completely based on top 2 scores from each team. I want the same function as the ROW() PARTITION BY function is SQL sever. I just want that function to be implemented via JOINS and SUB QUERIES and not directly. Please check and correct the query mention to obtain an output of this nature.

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 14:04:33

[code]
select * from team T
cross apply(select top 2 B.BowlerID,S.RawScores,B.BowlerFirstName + '' + B.BowlerLastName
from Bowler_Scores S inner join Bowlers B on B.BowlerID=S.BowlerID
where T.teamId=B.teamId order by S.RawScores desc)T1
[/code]

PBUH

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 15:08:04

I am using Microsoft Access 2007. Cross apply doesn't seem to work in there. Can you suggest an alternate solution?

Thanks for your effort though.

Anubhav

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 15:09:24

quote:
Originally posted by anubhavmax

I am using Microsoft Access 2007. Cross apply doesn't seem to work in there. Can you suggest an alternate solution?

Thanks for your effort though.

Anubhav

Now you are telling me...

PBUH

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 15:15:10

LOL...oops.. My Bad..

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 15:23:09

quote:
Originally posted by anubhavmax

LOL...oops.. My Bad..

It's not funny.

From next time please take care to post the appropriate details at the first so that one does not end up wasting his or her time trying to help you..

PBUH

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 15:25:30

Actually I had no idea that this would happen, as MS - Access and SQL server both are from Microsoft, I thought the command would be compatible on both. Sorry for the problem. New to SQL , so a bit ignorant.

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 15:35:00

Does this work?


select *, 
(select top 2 B.BowlerID
from Bowler_Scores S inner join Bowlers B on B.BowlerID=S.BowlerID
where T.teamId=B.teamId order by S.RawScores desc) BowlerId
from team T

PBUH

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 21:54:22

The message returned is "The Subquery can return only one row". The command is not even executing.

anubhavmax
Starting Member

10 Posts

Posted - 2010-10-24 : 22:02:06

Even this code is executing but not giving the desired result

SELECT Bowlers.BowlerID, Bowlers.BowlerFirstName, Bowlers.BowlerLastName, Bowlers.TeamID, Teams.TeamName, Bowler_Scores.RawScore
FROM (Bowlers LEFT JOIN Bowler_Scores ON Bowlers.BowlerID = Bowler_Scores.BowlerID) RIGHT JOIN Teams ON Bowlers.TeamID = Teams.TeamID
WHERE Bowlers.BowlerID IN
(SELECT TOP 2 BC.BowlerID FROM Bowler_Scores BS INNER JOIN Bowlers BC ON BS.BowlerID = BC.BowlerID WHERE Teams.TeamID = BC.TeamID ORDER BY BS.RawScore DESC);
[/hr]

Sachin.Nand

2937 Posts

Posted - 2010-10-25 : 00:45:55

quote:
Originally posted by anubhavmax

The message returned is "The Subquery can return only one row". The command is not even executing.

That's the reason I HATE Access.

PBUH

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-25 : 05:19:49

Try this:


SELECT B.BowlerID, B.BowlerFirstName, B.BowlerLastName, T.TeamID, T.TeamName, BS.RawScore
  FROM Teams AS T
       LEFT OUTER JOIN
       Bowlers AS B
       ON B.TeamID = T.TeamID
       LEFT OUTER JOIN
       Bowler_Scores AS BS
       ON BS.BowlerID = B.BowlerID
 WHERE B.BowlerID IN(SELECT TOP 2 BS2.BowlerID
                       FROM Teams AS T2
                            LEFT OUTER JOIN
                            Bowlers AS B2
                            ON B2.TeamID = T2.TeamID
                            LEFT OUTER JOIN
                            Bowler_Scores AS BS2
                            ON BS2.BowlerID = B2.BowlerID
                      WHERE T2.TeamID = T.TeamID
                      ORDER BY BS2.RawScore DESC)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources