Link to home
Start Free TrialLog in
Avatar of MikeGinMn
MikeGinMnFlag for United States of America

asked on

SQL Query Question

I have a query that returns information from a table.  However it returns info twice.  The only difference is the members of the team and reversed.  Is there a way to just take every other record so I can display info on an asp page

Here is query

SELECT     TOP (10000) dbo.[Bowling-Team-Mbr1].Games, dbo.[Bowling-Team-Mbr1].[Event name], dbo.[Bowling-Team-Mbr1].[Fin div], dbo.[Bowling-Team-Mbr1].Team,
                      dbo.[Bowling-Team-Mbr1].Name AS P1Name, dbo.[Bowling-Team-Mbr1].[Final score] AS P1Score, dbo.[Bowling-Team-Mbr1].Place AS P1Place,
                      dbo.[Bowling-Team-Mbr2].Name AS P2Name, dbo.[Bowling-Team-Mbr2].[Final score] AS P2Score, dbo.[Bowling-Team-Mbr2].Place AS P2Place, CONVERT(INT,
                      dbo.[Bowling-Team-Mbr2].[Final score]) + CONVERT(INT, dbo.[Bowling-Team-Mbr1].[Final score]) AS CalcTotal
FROM         dbo.[Bowling-Team-Mbr1] INNER JOIN
                      dbo.[Bowling-Team-Mbr2] ON dbo.[Bowling-Team-Mbr1].Games = dbo.[Bowling-Team-Mbr2].Games AND
                      dbo.[Bowling-Team-Mbr1].[Event name] = dbo.[Bowling-Team-Mbr2].[Event name] AND dbo.[Bowling-Team-Mbr1].[Fin div] = dbo.[Bowling-Team-Mbr2].[Fin div] AND
                      dbo.[Bowling-Team-Mbr1].Team = dbo.[Bowling-Team-Mbr2].Team AND dbo.[Bowling-Team-Mbr1].Name <> dbo.[Bowling-Team-Mbr2].Name
ORDER BY dbo.[Bowling-Team-Mbr1].Games, dbo.[Bowling-Team-Mbr1].[Event name], dbo.[Bowling-Team-Mbr1].[Fin div], CalcTotal DESC

which gives me

2011 Area 09 Bowling      Doubles      TradDbls-1      MN Valley 3      Welter, Nicholas      395       1st place      Hutchens, Michael      402       1st place      797
2011 Area 09 Bowling      Doubles      TradDbls-1      MN Valley 3      Hutchens, Michael      402       1st place      Welter, Nicholas      395       1st place      797
2011 Area 09 Bowling      Doubles      TradDbls-1      MN Valley 6      Tisdel, Linda      212       3rd place      MacKie, Krista      314       3rd place      526
2011 Area 09 Bowling      Doubles      TradDbls-1      MN Valley 6      MacKie, Krista      314       3rd place      Tisdel, Linda      212       3rd place      526

As you see 1st record is MN Valley 3 and 2nd record is also MN Valley 3 the names are just reversed.

Or is there a way just to get the query to give the team only once
Avatar of ezinder
ezinder

that's really something that should be done on the middle-tier.. but hey.. to each their own! :)

You can do this in several ways..

-- 1. declare a @tmp table with an identity like so:
declare @tmpReturnDataSet (row_id int identity(0, 1), YourColumn1, YourColumn2... etc)

-- 2. load the tmp table:
insert into @tmpReturnDataSet(YourColumn1, YourColumn2)
select YourColumn1, YourColumn2
from YourTable
Where ...

-- 3. Return both left and right datasets as one..
select a.*, b.*
from @tmpReturnDataSet a
full outer join @tmpReturnDataSet b on b.Row_id = a.Row_id+1 and b.row_id%2=1

where a.row_id%2=0


You can also try ROW_NUMBER() CTE.. but that's a bit more complex..
ASKER CERTIFIED SOLUTION
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MikeGinMn

ASKER

Thank you for the help - I'm still somewhat new to SQL so I appreciate the help.  I will try the other one as well later so I can hopefully learn more.  What is meant by Middle-Tier out of curosity