Link to home
Start Free TrialLog in
Avatar of JElster
JElsterFlag for United States of America

asked on

SQL Server 2005 - How to query 3 top sales persons

Hi.. I have a table of SALES data. It looks like this.
I need to return the Top 3 sales persons in each state.  
I also need to return the names in a single row like this.

CA            BOB, JANE,JON

any idea?
thanks


STATE       SALES PERSON           AMOUNT
CA              JON                           100
CA             JANE                           200
CA            BOB                            500
CA             KAREN                         50
VA              MARK                         10
VA              FRED                         100
VA             RALPH                       600
VA              ROB                            30
SOLUTION
Avatar of Ross Turner
Ross Turner
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
Probably a better way to do it but this is what came to mind...

Declare @SALES1 varchar(20)
Declare @SALES2 varchar(20)
Declare @SALES3 varchar(20)

set @SALES1 = select top 1 SALESPERSON from SALES 
set @SALES2 = select top 1 SALESPERSON from SALES where SALESPERSON <> @SALES1
set @SALES3 = select top 1 SALESPERSON from SALES where SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2

select (@SALES1 + ',' + @SALES2 + ',' + @SALES3)

Open in new window

jpgobert:

your query doesn't take into account the state or amount so all it does in essence is a select the top 3 and concat it on one line...
My bad I forgot to add the where... sorry about that...

I'll re-post an update shortly...
Better?  Yours definitely is several grades higher than me but I'm no DBA...

Just selecting the final dataset from the temp table... not sure what the OP wants to do with it once it is compiled...

Declare @State varchar(2)
Create Table #tmpSALES (State varchar(2), SalesPeople varchar(60))

Declare State_Cursor Cursor for
Select distinct state from SALES

Open State_Cursor
Fetch Next From State_Cursor into @State
While @@Fetch_Status = 0
Begin

	Declare @SALES1 varchar(20)
	Declare @SALES2 varchar(20)
	Declare @SALES3 varchar(20)

	set @SALES1 = select top 1 SALESPERSON from SALES where State = @State
	set @SALES2 = select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1
	set @SALES3 = select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2

	Insert Into #tmpSALES values (@State, (@SALES1 + ',' + @SALES2 + ',' + @SALES3))

Fetch Next from State_Cursor into @State
End

Close State_Cursor
Deallocate State_Cursor

Select * from #tmpSALES

Drop Table #tmpSALES

Open in new window

Avatar of JElster

ASKER

Hi..
I'm getting the following

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Msg 195, Level 15, State 10, Line 4
'Rank' is not a recognized function name.
Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'XML'.

Using Sql Server 2008

thanks
Avatar of JElster

ASKER

I think the version of SQL Server is 2005.
ah right i just double checked that it worked on sql 2005 and it does so can you post your code and i give it a quick look
Avatar of JElster

ASKER

Sorry it maybe sql 2000... not really sure.. I'm connected remotely.
Ahh now that makes sense to the rank and xml functions not working

run
select  @@Version

Open in new window


if it sql 2000 my code won't work at all.....
mine will
Avatar of JElster

ASKER

Yeah.. it's 2000
Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)   May 11 2012 18:41:14   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Avatar of JElster

ASKER

set @SALES1 = select top 1 SALESPERSON from SALES where State = @State


Get
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'select'.
My bad... the select statement needs to be enclosed in ()

set @SALES1 = (select top 1 SALESPERSON from SALES where State = @State)
set @SALES2 = (select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1)
set @SALES3 = (select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2)

Open in new window

Avatar of JElster

ASKER

Hi..
It does not return anything if only 1 sales person... any ideas?
thx again
Are you getting values otherwise?
I'm pondering.... mind set is for sql 2005 + thou ....eeeekkkk
ASKER CERTIFIED SOLUTION
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
This is another version that should graft just using loops rather than cursor

DROP TABLE #TempTable 
Create Table #TempTable
([ID] int Identity,
 [State] Varchar(30)
 )
DROP TABLE #TempHolding 
Create Table #TempHolding
([ID] int Identity,
 [State] Varchar(500),
 [Details] Varchar(500)
 )

Insert into #TempTable
SELECT DISTINCT STATE FROM [dbo].[TABLE_NAME]

Declare @SALES1 varchar(20)
Declare @SALES2 varchar(20)
Declare @SALES3 varchar(20)
Declare @State varchar(20)
DECLARE @Loop int 
Set @Loop = 1



While  @Loop <= (select count(*) from #TempTable)
Begin
Set  @SALES1 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) ORDER BY [State],[Amount] DESC)
Set  @SALES2 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) and SALESPERSON <> @SALES1 ORDER BY [State],[Amount] DESC)
Set  @SALES3 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2 ORDER BY [State],[Amount] DESC)
SET	 @State  = (select state from #TempTable where id = @loop)
Insert into #tempholding 
values(@State,@SALES1 + ',' + @SALES2 + ',' + @SALES3) 
SET  @loop	= @loop + 1
End

Select * from #tempholding

Open in new window