MS SQL help

[Arpit]

A newbie here with SQL and thanks in advance.

I have a structure where I've got 1 table of employee ID and branch ID and 1 table of customer ID and asset amount.

I would like to find out a list of 5 customer IDs with greatest assets for each of the employee ID. How do I go about doing this? I've run out of ideas with what little I know of SQL.

[Rohdekill]

A newbie here with SQL and thanks in advance.

I have a structure where I've got 1 table of employee ID and branch ID and 1 table of customer ID and asset amount.

I would like to find out a list of 5 customer IDs with greatest assets for each of the employee ID. How do I go about doing this? I've run out of ideas with what little I know of SQL.

Going by what you've typed... you can't. You need a relation between the two tables. Or, are you leaving out columns on us?

[____]

It sounds like you want each employee to have a collection of customers. If this is the case you will want to add a column to the customer table that contains the id of the employee that services that customer. You can then join the two tables on that column.

[Arpit]

i guess i wanted to create a simple example but the expanded version is as follows:

the customer table has the following:

customerID, employeeID, Assets, other financial figures

the employee table is a temp table that contains the employee ID and branch ID

my problem is more to do with syntax and algorithm than with the logic. the only way i can think of getting the 'top 5 client stats' is by manually putting the customer id in my 'WHERE' clause. something like:

SELECT TOP 5 assets

FROM customertable

WHERE EMPLOYEEID=1234

i have a list of 30 employees and i don't wanna do this manually...so i need something like:

go through employee table

for each employeeID, give me top 5 customers with biggest assets

i hope this helps...

[Mr. Bean]

select top 5
	customerid, assets
from
	customers
where employeeid = 1234
	order by assets desc

[Arpit]

thanks mr. bean but i already have that...my issue is that i have 30 employee ids and i don't want to do this manually. how do i "automate" that for 30 employee ids?

[hsraffety]

don't limit it to just one employee id?

[Arpit]

yes but how do i show top 5 for EACH?...if i say WHERE EMPLOYEEID IN (1234, 5678), i still get top 5 results. i want top 5 for each employee ID

[HeyRatFans]

Sounds like you need a derived table.

	SELECT employee_id, employee_name
	   FROM employees
  LEFT JOIN (SELECT customerid,
					assets,
					employee_id
			   FROM customers
		   ORDER BY assets DESC
			  LIMIT 5) AS customers USING (employee_id)
	  WHERE employee_id IN (1234, 1235);

Difficult to say if that will work without seeing your database schema, but hopefully it'll give you the idea.

Edit: Just realised this is MS SQL, but that should work, I think :$

[Mr. Bean]

Ahh, ok. Well, it sounds to me like you will need some kind of control structure to loop through each distinct employee id.

If you know .net or java, you can do this pretty easily. Simply create a basic console application that follows this pseudo workflow:

- declare and initialize a List<> of ints

- Setup sql connection

- make a call to the database, retrieving all the unique employee ids

- while(sqlreader.read()), throw emp id into the List<>

- loop through each employee id in the List<>, making another call to the database, passing the employee id in as a parameter. Make the sql take the top 5 for just that one employee, and insert them into a temp/rollup table.

- go back into Sql Manager and do a select * from your temp/rollup table

You can also do this in SQL, but I think it will be messier.

[Rohdekill]

no. it won't be messier and take longer. Just wrap it in a loop. In order to receive the data back, you'll need to create a holding table. Something similar to this (I just typed this out, untested, but you should get the general concept):

declare @min as int,

@max as int

create table #temp (Employee_ID int, Employee_Name nvarchar(50), customer_ID int, asset nvarchar(50))

set min= (select min(Employee_ID) from [Employees])

set max= (select max(Employee_ID) from [Employees])

while @min <=@max

BEGIN

insert into #temp (Employee_ID Employee_Name, Customer_ID, asset)

values

(SELECT employee_id, employee_name

FROM employees

LEFT JOIN (SELECT customerid, assets)

FROM customers LIMIT 5)

AS customers USING (employee_id)

WHERE employee_id = @min)

set @min = (select min(Employee_ID) from [Employees] where Employee_ID > @min)

END

select * from #TEMP order by Employee_ID, assets DESC

drop table #temp

END

[loople]

Urgh, loops in SQL? Seriously? Stick to set-based programming wherever possible!

DecoyDuck's idea of using a derived table is probably a good solution to this.

[Rohdekill]

Urgh, loops in SQL? Seriously? Stick to set-based programming wherever possible!

DecoyDuck's idea of using a derived table is probably a good solution to this.

Don't tell me you've never had to loop within SQL. I've seen and used this very code at several fortune 500 companies...and not only coded by myself. It's fast, extremely efficient, and takes a few lines of code.

[Arpit]

thanks guys...i'll try both ways when i go to work tomorrow and let you know how it works out. i didn't know of the "limit by" command...that might come in handy.

[Mr. Bean]

Don't tell me you've never had to loop within SQL. I've seen and used this very code at several fortune 500 companies...and not only coded by myself. It's fast, extremely efficient, and takes a few lines of code.

Agreed. Although it can be a bit messy compared to a traditional programming language, it is more efficient as it runs native and encapsulated within SQL. Using an external api to access data would be less efficient and adds external dependency to the solution, but probably quicker and cleaner to write -- especially for more complex scenarios. Just a matter of preference and design choice.

[loople]

:blush: I must confess I have used loops in my SQL on a couple of occassions, but it doesn't make it best practice :).

Funnily enough I had the same problem as the OP crop up at work today. The following is my MS SQL (2005+) code (it will work for the OP given some minor modification):

	
	WITH OrderItems AS (
		SELECT oo.ID, s.ID AS 'StaffID', oi.ItemTitle,
				ROW_NUMBER() OVER (PARTITION BY oo.ID ORDER BY oo.ID) AS 'Counter'	-- The counter counts from 1:n for each order
		FROM 
			OrderOverview oo JOIN Staff s ON s.ID=oo.Person LEFT JOIN OrderItems oi ON oi.OrderID = oo.ID
	)

	SELECT oo.*, s.FirstName + ' ' + s.Surname AS 'Name', oi.ItemTitle
	FROM OrderOverview oo 
		JOIN Staff s ON oo.StaffID = s.ID
		LEFT JOIN OrderItems oi ON s.ID = oi.StaffID AND oi.Counter IN (1,2,3,4,5) -- Get the first 5 items for each order
	WHERE [whatever]		
	ORDER BY DateModified ASC

This ficticious example would return a list of all Orders, joined to the first 5 Items in those orders. Orders with more than 5 items would only show the first 5 in the results, orders with 5 items or fewer would show all items for that order. Change table and column names to suit :)

(edit: Updated code to fix bug :blush:)

Edited May 11, 2009 by loople

[Arpit]

thanks loople and other guys..

i ended up using a cursor to make it work so it basically goes through each employee ID and finds me top 5 customer stats. then i put the stuff in a temp table so it comes out clean.

appreciate the help nonetheless!

[loople]

No problem, friendly advice though, if this is for work or a school project try to implement something like my suggestion. Cursors although easy to understand are horribly inefficient on larger datasets - AFAIK the solution I posted will be very efficient. If it's a personal-style project and you know the database won't suffer in terms of performance then I'd perhaps keep the cursor if you've got other better things to do with your time! :)