Top N Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Top N Question

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-09 : 16:55:19

Hi I am doing a Store procedure that returns the 100 item with more demand, how can I do to put the top number has a parameter.

This I what I was thinking but don't work

declare @x as int
set @x=10
SELECT top @x dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION, SUM(dbo.OEINVCLINE.QUANTITY*dbo.OEINVCLINE.UNIT_PRICE) AS TOTAL,
dbo.OEINVCLINE.UNIT_PRICE

FROM dbo.OEINVCLINE INNER JOIN
dbo.OEINVOICE ON dbo.OEINVCLINE.INVC_NUMBER = dbo.OEINVOICE.INVC_NUMBER

Thanks for your help!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 17:00:32

Not that I'm an advocate of dynamic sql, but....

declare @x as int, @SQL varchar(1000)

set @x=10

Select @SQL = 'SELECT top '+ Convert(varchar(4),@x)
+ ' dbo.OEINVCLINE.ITEM,dbo.OEINVCLINE.DESCRIPTION'
+' , SUM(dbo.OEINVCLINE.QUANTITY*dbo.OEINVCLINE.UNIT_PRICE) AS TOTAL'
+' , dbo.OEINVCLINE.UNIT_PRICE'
+' FROM dbo.OEINVCLINE INNER JOIN'
+' dbo.OEINVOICE ON dbo.OEINVCLINE.INVC_NUMBER ='
+' dbo.OEINVOICE.INVC_NUMBER'

Exec(@SQL)

Should do the trick...but what are you really trying to do again?

Brett

8-)

Edited by - x002548 on 04/09/2003 17:01:36

Edited by - x002548 on 04/09/2003 17:02:17

Edited by - x002548 on 04/09/2003 17:03:02

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-09 : 17:10:03

declare @x as int
set @x=10

set rowcount @x
SELECT dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION, SUM(dbo.OEINVCLINE.QUANTITY*dbo.OEINVCLINE.UNIT_PRICE) AS TOTAL,
dbo.OEINVCLINE.UNIT_PRICE

FROM dbo.OEINVCLINE INNER JOIN
dbo.OEINVOICE ON dbo.OEINVCLINE.INVC_NUMBER = dbo.OEINVOICE.INVC_NUMBER
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 17:16:07

Didn't think that would work....But it DOES!

Nigel, isn't it past your bedtime by now? The pubs must be closed.

shifis: I'm still interested in what you're trying to do...limiting a set just basically ignores anything left. What are you planning to do with the rest of the rows? Also by doing an agregate, you're essentially misreporting the data about the group.

Just curious.

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-09 : 17:17:50

Good point - goodnight.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-09 : 20:32:22

Ok
I am working in a chain of supermarket, they need a report that tell them what items are the most sold, in this case in quantity.
This is the Store Procedure:

CREATE PROCEDURE SP_RMASVENDIDOS
@Locat AS varchar(20), @Fe1 as datetime, @Fe2 as datetime -- quiero agragar otro parametro para pasar la cantidad de codigos que quieren en el reporte
-- no me lo toma en el top
AS

SELECT top 100 dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION, SUM(dbo.OEINVCLINE.QUANTITY) AS TOTAL,
dbo.OEINVCLINE.UNIT_PRICE

FROM dbo.OEINVCLINE INNER JOIN
dbo.OEINVOICE ON dbo.OEINVCLINE.INVC_NUMBER = dbo.OEINVOICE.INVC_NUMBER

WHERE dbo.OEINVCLINE.LOCATION=@Locat AND (dbo.OEINVOICE.INVOICE_DATE >=@Fe1 AND dbo.OEINVOICE.INVOICE_DATE<=@Fe2)

GROUP BY dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION,dbo.OEINVCLINE.UNIT_PRICE

ORDER BY TOTAL DESC
GO

Edited by - shifis on 04/09/2003 20:35:16

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 21:08:02

Nigel,

Hope I wasn't offensive..

shifis: I too now have too much of an addiction to this site...I'll check out your response in the am...

Time for Devils Hockey...actually probably the 3rd period by now...

[qoute]
Ok
I am working in a chain of supermarket, they need a report that tell them what items are the most sold, in this case in quantity.
[/quote]

But at first glance I would say for what time period. There needs to be something that you limit your reporting information to. For example the week of x. If you don't have a datetime column that identifies this, then you'd be stuck with sales forever. I'm sure you most have a sold date.

Let us know.

Thanks

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-09 : 21:14:31

God I hate this site....

Must.......turn........off.......computer.......

Nigel (or anyone) Do you think @@rowcount limit the number of rows in a group by?

I'll have to test it, and it seems illogical, but as I like to say, "Hey, its Microsoft, what'dya expect".

I'm still flabergated about "SELECT @x = @x + Col1 From Table"

Brett

8-)

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-09 : 21:22:00

I would imagine you would want to multiply your quantity by the price to get your total and order by that as in Nigel's example above but add

ORDER BY TOTAL DESC

Brett, if you mean limit the recordset before it groups - nope. Try it out and look at the number of rows in the execution plan Query Analyzer.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-10 : 04:35:11

>> Nigel (or anyone) Do you think @@rowcount limit the number of rows in a group by?

set rowcount and top both limit the results after the result set is created. It's like putting the result into a temp table then selecting the top records using the order by (in fact that's often what happens).

>> Hope I wasn't offensive..
??? You were right - I needed to leave.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 09:02:54

Thanks Nigel.

So it (the optimizer) will do all the work, and because of TOP or @@ROWCOUNT, it will discount the rest. I'm just curious now. Because SQL Server does not incorporate a sort (at least when it produces the results) of a group by, how does it know which records to produce when using those options? I mean in DB2, a group by will produce the results in asc order. I'm just curious as to how it can do the grouping with out the sort?

EDIT Never Mind: I see your doing a sort in the SQL Statement.
What Nigel gave you should work perfectly.

Brett

8-)

Edited by - x002548 on 04/10/2003 09:44:04

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-10 : 09:13:44

shifis, take a look at this thread about getting the top N of a group.

Jay White
{0}

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-11 : 14:48:40

Thank you all for your help, now my store procudure is:

CREATE PROCEDURE SP_RMASVENDIDOS
@Locat AS varchar(20), @Fe1 as datetime, @Fe2 as datetime, @x as int -- quiero agragar otro parametro para pasar la cantidad de codigos que quieren en el reporte
-- no me lo toma en el top
AS
set rowcount @x
SELECT dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION, SUM(dbo.OEINVCLINE.QUANTITY) AS TOTAL,
dbo.OEINVCLINE.UNIT_PRICE

FROM dbo.OEINVCLINE INNER JOIN
dbo.OEINVOICE ON dbo.OEINVCLINE.INVC_NUMBER = dbo.OEINVOICE.INVC_NUMBER

WHERE dbo.OEINVCLINE.LOCATION=@Locat AND (dbo.OEINVOICE.INVOICE_DATE >=@Fe1 AND dbo.OEINVOICE.INVOICE_DATE<=@Fe2)

GROUP BY dbo.OEINVCLINE.ITEM, dbo.OEINVCLINE.DESCRIPTION,dbo.OEINVCLINE.UNIT_PRICE

ORDER BY TOTAL DESC
set rowcount 0
GO

If you have any comment I be glad to read them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources