JElster

asked on 5/22/2013

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

Microsoft SQL ServerMicrosoft SQL Server 2005

Last Comment

Ross Turner

8/22/2022 - Mon

SOLUTION

Ross Turner

5/22/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

John Gobert

5/22/2013

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

Ross Turner

5/22/2013

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...

John Gobert

5/22/2013

My bad I forgot to add the where... sorry about that...

I'll re-post an update shortly...

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

John Gobert

5/22/2013

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

JElster

5/22/2013

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

JElster

5/22/2013

ASKER

I think the version of SQL Server is 2005.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Ross Turner

5/22/2013

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

JElster

5/22/2013

ASKER

Sorry it maybe sql 2000... not really sure.. I'm connected remotely.

Ross Turner

5/22/2013

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.....

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

John Gobert

5/22/2013

mine will

JElster

5/22/2013

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)

JElster

5/22/2013

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'.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

John Gobert

5/22/2013

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

JElster

5/22/2013

ASKER

Hi..
It does not return anything if only 1 sales person... any ideas?
thx again

John Gobert

5/22/2013

Are you getting values otherwise?

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Ross Turner

5/22/2013

I'm pondering.... mind set is for sql 2005 + thou ....eeeekkkk

ASKER CERTIFIED SOLUTION

John Gobert

5/22/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Ross Turner

5/22/2013

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