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
LVL 1
JElsterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jpgobertConnect With a Mentor Commented:
Ok... just built a quick test table and I see what you mean.  We just need to add isnull to the setting of the sales variables.  See below.

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 = ISNULL((select top 1 SALESPERSON from SALES where State = @State),'')
	set @SALES2 = ISNULL((select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1),'')
	set @SALES3 = ISNULL((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

0
 
Ross TurnerConnect With a Mentor Management Information Support AnalystCommented:
Hi i knocked this example up for you

http://sqlfiddle.com/#!3/d7c66/3

With CTE As (
 Select * from (Select 
* ,
Rank() OVER (PARTITION BY  state order by amount desc) as Ranked
from Table_name
) Temp1 
Where ranked in (1,2,3)
)
Select distinct 
State,
STUFF(
               (SELECT      ',' + SubTableUser.Salesperson 
               FROM      CTE AS SubTableUser
               WHERE      SubTableUser.STATE = CTE.STATE
                
               FOR XML PATH('')), 1, 1, '') AS Comments
from CTE

Open in new window


Create table TABLE_NAME 
(STATE varchar(30),SALESPERSON varchar(30),AMOUNT int) 


INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','JON','100');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','JANE','200');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','BOB','500');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','KAREN','50');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','MARK','10');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','FRED','100');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','RALPH','600');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','ROB','30');

Open in new window

0
 
jpgobertCommented:
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

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ross TurnerManagement Information Support AnalystCommented:
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...
0
 
jpgobertCommented:
My bad I forgot to add the where... sorry about that...

I'll re-post an update shortly...
0
 
jpgobertCommented:
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

0
 
JElsterAuthor Commented:
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
0
 
JElsterAuthor Commented:
I think the version of SQL Server is 2005.
0
 
Ross TurnerManagement Information Support AnalystCommented:
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
0
 
JElsterAuthor Commented:
Sorry it maybe sql 2000... not really sure.. I'm connected remotely.
0
 
Ross TurnerManagement Information Support AnalystCommented:
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.....
0
 
jpgobertCommented:
mine will
0
 
JElsterAuthor Commented:
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)
0
 
JElsterAuthor Commented:
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'.
0
 
jpgobertCommented:
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

0
 
JElsterAuthor Commented:
Hi..
It does not return anything if only 1 sales person... any ideas?
thx again
0
 
jpgobertCommented:
Are you getting values otherwise?
0
 
Ross TurnerManagement Information Support AnalystCommented:
I'm pondering.... mind set is for sql 2005 + thou ....eeeekkkk
0
 
Ross TurnerManagement Information Support AnalystCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.