Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server 2005 - How to query 3 top sales persons

Posted on 2013-05-22
19
383 Views
Last Modified: 2013-05-22
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
0
Comment
Question by:JElster
  • 7
  • 6
  • 6
19 Comments
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 200 total points
ID: 39187122
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
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187129
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
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187165
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:jpgobert
ID: 39187183
My bad I forgot to add the where... sorry about that...

I'll re-post an update shortly...
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187203
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
 
LVL 1

Author Comment

by:JElster
ID: 39187212
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
 
LVL 1

Author Comment

by:JElster
ID: 39187215
I think the version of SQL Server is 2005.
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187224
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
 
LVL 1

Author Comment

by:JElster
ID: 39187234
Sorry it maybe sql 2000... not really sure.. I'm connected remotely.
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187239
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
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187246
mine will
0
 
LVL 1

Author Comment

by:JElster
ID: 39187248
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
 
LVL 1

Author Comment

by:JElster
ID: 39187250
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
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187258
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
 
LVL 1

Author Comment

by:JElster
ID: 39187395
Hi..
It does not return anything if only 1 sales person... any ideas?
thx again
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187441
Are you getting values otherwise?
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187445
I'm pondering.... mind set is for sql 2005 + thou ....eeeekkkk
0
 
LVL 8

Accepted Solution

by:
jpgobert earned 300 total points
ID: 39187510
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
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187633
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 24
Need help with T-SQL on SQL Server 2014 9 37
Are triggers slow? 7 11
sql server query 18 36
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question