Top X question 2

[bessebo]

I want to report information in this manner. I want to include only the top 3 occurrences of customers of each vertical market but I want to include 3 from each vertical market. So I have a population like the following:

verticalmarket, shiptoname


I want to create a select statement where the results are like the following:

Vertical Market A John's Meats 10,000
Vertical Market A Bill's Poultry 8,000
Vertical Market A Jill's Pastry 6,000

Vertical Market B ACME Produce 12,000
Vertical Market B BB Meats 9,500
Vertical Market B CC Steaks 2,500

But if I do the following:

select top 3 verticalmarket,shiptoname,count(*)
from table1
group by verticalmarket,shiptoname
order by verticalmarket asc,shiptoname asc,count(*) desc

will only give me the top 3 occurrences of all the records not in each vertical market. Can anyone suggest how to get the results I am looking for (top 3 for each vertical market) in one sql statement?

 

[vongrunt]

SQL2000 or 2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bessebo]

2000...

 

[vongrunt]

Ouch then... this requires ranking. Is "one SQL statement" absolute requirement?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Nagrom]

I hate to speak in absolutes, but I think it's safe to do so here: There's no way to do that with a single SQL statement.

That being said, you can "simulate" a single SQL statement (to your code, at least) by packaging the necessary steps into a stored procedure and then executing that stored proc from your code. The "one sql statement" is the call to the stored procedure.

 

[vongrunt]

It is possible to do that with a single SQL statement - assuming you can live with repeatable code (OK, this can be "solved" vith view).

(hint: self-join)

Btw. this is MUCH easier to do w/ SQL2005

(hint: CTE, ranking functions)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Nagrom]

vongrunt,

Can you post an example of how you would do that? I've been tasked with something similar in the past and never found a single statement that could do it. I'd love to find a solution that didn't require cursors or some other kind of looping (which is what I ended up implementing in a stored proc). It would be a great time (and processor!) saver.

 

[vongrunt]

Here is example (Northwind DB, top 3 shipping countries per employee):

select A.EmployeeID, A.ShipCountry, A.OrderCount, count(*) as rank
from
(	select EmployeeID, ShipCountry, count(*) as OrderCount
	from Orders
	group by EmployeeID, ShipCountry
) A
inner join 
(	select EmployeeID, ShipCountry, count(*) as OrderCount
	from Orders
	group by EmployeeID, ShipCountry
) B
on A.EmployeeID = B.EmployeeID and A.OrderCount<=B.OrderCount
group by A.EmployeeID, A.ShipCountry, A.OrderCount
having count(*) <= 3
order by A.EmployeeID, rank

Plz note this returns top 3 distinct ranks per employee - basically the same as DENSE_RANK() in SQL2005.

Repeated code (derived tables A and B) can be placed into view. That reduces code size a bit.

Of course in SQL2005 this is easier to do - with reused CTE, or single query with ranking function, or with UDF and APPLY() thing.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Correction: this does the same as RANK() - to do dense variation, use COUNT(DISTINCT B.OrderCount) AS rank instead.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bessebo]

Thank you all.

 

[Nagrom]

vongrunt,

Shouldn't that be "A.OrderCount>=B.OrderCount" rather than "A.OrderCount<=B.OrderCount"? (not trying to flame - just clarifying because I want to use a statement like that)

A very elegant solution - a gold start for you, Sir (or Madam, as the case may be - please pardon any unintentional gender bias)!

 

[vongrunt]

> Shouldn't that be "A.OrderCount>=B.OrderCount" rather than "A.OrderCount<=B.OrderCount"?

I think not - because rows with greatest OrderCount should have highest rank (lowest rank value). That way highest OrderCount per employee gets rank 1 and filtering with HAVING clause (count(*) <= 3) becomes easy.



------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[deb13]

Hi Bessebo, this is not related to this thread but one you did back in 2003 - I'm trying to add new user to CE10 but new user in another domain (Spain) - I use AD authentication and seamless log in but cannot see the user/groups on the other domain... did you ever manage to resolve this?

Thanks

Deb
Sheffield, UK

 

[bessebo]

Deb,
Pardon my ignorance but I'm not sure what AD authentication is. When I add a new user I go to the Crystal Enterprise server and click on Administrative Tools, Computer Management. Expand Local Users and Groups and click on Groups. Right-click on Crystal BT Users and click on Add to Group. Then I click Add and where it says Look In at the top of the Select Users or Groups I click on the down arrow and choose the Domain that you want to add a user from. This is where you will choose the domain(Spain). Then click on the user you want to add and click on Apply.
THere is one more thing you have to do which is to go into Crystal Management Console and click on Manage Authorization, click on the Windows NT tab and make sure the Radio Button Create a new account for every added NT alias is enabled and click on Update. This will create the user and you should see that user under Manage Users and in there you can assign the proper security and make the user either a Named or Concurrent User. That should work.
Still not sure what AD authentication is though.

Regards,
Bessebo

 

[deb13]

Hi Bessebo, sorry - AD = Active Directory - its the other tab on the authentication screen, that's the one we user for adding new global groups etc. I'll try what you have suggested and get back to you. Kind regards, Deb

 

[bessebo]

That was foolish of me. We do not user Active Directory but I'm guessing that the steps are similar. I do not have Active Directory as an option on the authentication screen. We are using CE9. What are u using?

 

[deb13]

Hi Bessebo

We are using CE10 - that suggestion worked - thank you very much - I'll thank you for the post even though it's not on this thread! Thanks again

Deb

 

[bessebo]

Deb,
Glad to help and glad it worked.

Regards,
Bob