MS SQL Server - TSQL top 10

Author

TSQL top 10

Brian K. Sheperd

2006-01-30, 9:23 am

If you use the top command, it will only give you the top x from the select
statement. How can you get the top 10 sales per product or even top 10
sales per product per territory? Does this have to be done with a stored
proceedure?

Thanks,
Brian

Jens

2006-01-30, 9:23 am

No, you can use correlated queries for this but unless you don=B4t show
us any DDL or table structure there is only a little chance that we
guess the right solution :-) http://www.aspfaq.com/5006

HTH, Jens Suessmeyer.

Brian K. Sheperd

2006-01-30, 1:23 pm

Sorry. This was something that I would need to do, but nothing finalized
with the DB as of yet.
Say that I had a customer table (CustID, CustName, Company, etc.), an orders
table (OrderID, CustID, ItemNum, ItemDesc, qyt, cost, categoryNum), and a
category table (CategoryNum, Description). I wanted to see the top 10
(based on cost) products ordered by customers. Maybe 1 step further would
be the same but also broken down by categories.

Thanks,
Brian

"Jens" <Jens@sqlserver2005.de> wrote in message
news:1138629902.548693.14820@g47g2000cwa.googlegroups.com...
No, you can use correlated queries for this but unless you don´t show
us any DDL or table structure there is only a little chance that we
guess the right solution :-) http://www.aspfaq.com/5006

HTH, Jens Suessmeyer.

David Portas

2006-01-30, 1:23 pm

Brian K. Sheperd wrote:
> Sorry. This was something that I would need to do, but nothing finalized
> with the DB as of yet.
> Say that I had a customer table (CustID, CustName, Company, etc.), an orders
> table (OrderID, CustID, ItemNum, ItemDesc, qyt, cost, categoryNum), and a
> category table (CategoryNum, Description). I wanted to see the top 10
> (based on cost) products ordered by customers. Maybe 1 step further would
> be the same but also broken down by categories.
>
> Thanks,
> Brian
>
>

Still not much of a spec to go on. No DDL, constraints or keys and no
clue what version you are using. Also "top 10 (based on cost) products
ordered by customers" seems to mean something different to what you
first asked for - sample data and expected results would have made your
requirements clearer.

Based on your previous post and assuming SQL Server 2005, try this
example:

CREATE TABLE orders (orderid INTEGER NOT NULL PRIMARY KEY /* ?? */,
custid INTEGER NOT NULL /* REFERENCES customers (custid) ?? */, itemnum
INTEGER NOT NULL /* REFERENCES items (itemid) */, itemdesc INTEGER NOT
NULL /* !! belongs in the "items" table? */, qty INTEGER NOT NULL, cost
INTEGER NOT NULL /* !! belongs in the "items" table? */, categorynum
INTEGER NOT NULL /* REFERENCES categories (categorynum) !! belongs in
the "items" table? */)

/* Top 10 products per customer by total cost: */

SELECT custid, itemnum, categorynum, total_cost
FROM
(SELECT custid, itemnum, categorynum, SUM(cost) AS total_cost,
DENSE_RANK() OVER (PARTITION BY custid ORDER BY SUM(cost) DESC) AS
rnk
FROM orders
GROUP BY custid, itemnum, categorynum) AS T
WHERE rnk <= 10 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Brian K. Sheperd

2006-01-30, 1:23 pm

"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1138646782.131728.160960@g49g2000cwa.googlegroups.com...
> Brian K. Sheperd wrote:
finalized[color=dark
red]
orders[color=darkred
]
a[color=darkred]
would[color=darkred]

>
> Still not much of a spec to go on. No DDL, constraints or keys and no
> clue what version you are using. Also "top 10 (based on cost) products
> ordered by customers" seems to mean something different to what you
> first asked for - sample data and expected results would have made your
> requirements clearer.
>
> Based on your previous post and assuming SQL Server 2005, try this
> example:
>
> CREATE TABLE orders (orderid INTEGER NOT NULL PRIMARY KEY /* ?? */,
> custid INTEGER NOT NULL /* REFERENCES customers (custid) ?? */, itemnum
> INTEGER NOT NULL /* REFERENCES items (itemid) */, itemdesc INTEGER NOT
> NULL /* !! belongs in the "items" table? */, qty INTEGER NOT NULL, cost
> INTEGER NOT NULL /* !! belongs in the "items" table? */, categorynum
> INTEGER NOT NULL /* REFERENCES categories (categorynum) !! belongs in
> the "items" table? */)
>
> /* Top 10 products per customer by total cost: */
>
> SELECT custid, itemnum, categorynum, total_cost
> FROM
> (SELECT custid, itemnum, categorynum, SUM(cost) AS total_cost,
> DENSE_RANK() OVER (PARTITION BY custid ORDER BY SUM(cost) DESC) AS
> rnk
> FROM orders
> GROUP BY custid, itemnum, categorynum) AS T
> WHERE rnk <= 10 ;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
This is an upcoming project. Nothing definitive yet. I know that this can
be done with TSQL, but it is beyond my knowledge/ability. I was looking to
see how it could be done; otherwise, I would have to create some front-end
such as VB. I posted the tables as an ad hoc template. The SQL version
would be 2000. Perhaps, I should hold off until I know more myself.

Thanks again,

Brian

Alexander Kuznetsov

2006-01-30, 8:23 pm

Brian,

it can be, and in many cases should be done in the back end. There are
many ways to accomplish it in SQL 2000, such as:

create table sales(salesId int, regionId int, amount float)
insert into sales values(1, 1, 100.)
insert into sales values(2, 1, 200.)
insert into sales values(3, 1, 300.)
insert into sales values(11, 11, 1100.)
insert into sales values(12, 11, 2100.)
insert into sales values(13, 11, 3100.)
go
select * from sales s
where salesId in(select top 2 s1.salesId from sales s1
where s1.regionId = s.regionId
order by amount desc)

salesId regionId amount

----------- -----------
-----------------------------------------------------
2 1 200.0
3 1 300.0
12 11 2100.0
13 11 3100.0

(4 row(s) affected)

go
drop table sales

for performance reasons, you might want to create an index on(regionId,
amount)

There are other ways which may in some cases perform better

Brian K. Sheperd

2006-01-30, 8:23 pm

"Alexander Kuznetsov" < AK_TIREDOFSPAM@hotma
il.COM> wrote in message
news:1138651137.103124.150870@g43g2000cwa.googlegroups.com...
> Brian,
>
> it can be, and in many cases should be done in the back end. There are
> many ways to accomplish it in SQL 2000, such as:
>
> create table sales(salesId int, regionId int, amount float)
> insert into sales values(1, 1, 100.)
> insert into sales values(2, 1, 200.)
> insert into sales values(3, 1, 300.)
> insert into sales values(11, 11, 1100.)
> insert into sales values(12, 11, 2100.)
> insert into sales values(13, 11, 3100.)
> go
> select * from sales s
> where salesId in(select top 2 s1.salesId from sales s1
> where s1.regionId = s.regionId
> order by amount desc)
>
> salesId regionId amount
>
> ----------- -----------
> -----------------------------------------------------
> 2 1 200.0
> 3 1 300.0
> 12 11 2100.0
> 13 11 3100.0
>
> (4 row(s) affected)
>
> go
> drop table sales
>
> for performance reasons, you might want to create an index on(regionId,
> amount)
>
> There are other ways which may in some cases perform better
>

Thanks Alexander,

Do you know of a good resource to expand my querying ability (book, website,
etc)?

Thanks,
Brian

Alexander Kuznetsov

2006-01-31, 3:23 am

Brian,

I think the resources you pay for may be much much better than free
information on free Web sites. About writing queries for SQL Server, I
personally would like Ken Henderson's books more than anything else.
Also a book by Robert Vieira is very very useful

Sponsored Links