Insert Into with Order By, won't work?

[Esoteric]

I am having issues with the following:

INSERT INTO [##PH_Results] (NAMEKEY, QTYSOLD, PRODUCT)
(
SELECT TOP 25 dbo.InvDetail.OMItemKey, SUM(dbo.InvDetail.OMItemQty) AS TotalCount,
dbo.Inventory.PRODUCT FROM dbo.InvHeader INNER JOIN dbo.InvDetail ON dbo.InvHeader.OMInvoiceNumber = dbo.InvDetail.OMInvoiceNumber
INNER JOIN dbo.Inventory ON dbo.InvDetail.OMItemKey = dbo.Inventory.NAMEKEY
WHERE (dbo.InvHeader.OMVoid = 0) AND (dbo.InvHeader.OMDate
BETWEEN '12/01/2006' AND '12/29/2006 23:59:59')
GROUP BY dbo.InvDetail.OMItemKey, dbo.InvDetail.OMItemDesc, dbo.Inventory.PRODUCT
ORDER BY SUM(OMItemQty) DESC
)

I run the query alone and its fine. Results that I need but when I dump it to the temp table I get error at ORDER. Am I not allowed to do this?

 

[gmmastros]

Try removing the parenthesis.

INSERT INTO [##PH_Results] (NAMEKEY, QTYSOLD, PRODUCT)
[green]-- Open Parenthesis removed[/green]
SELECT TOP 25 dbo.InvDetail.OMItemKey, SUM(dbo.InvDetail.OMItemQty) AS TotalCount, 
dbo.Inventory.PRODUCT FROM dbo.InvHeader INNER JOIN dbo.InvDetail ON dbo.InvHeader.OMInvoiceNumber = dbo.InvDetail.OMInvoiceNumber 
INNER JOIN dbo.Inventory ON dbo.InvDetail.OMItemKey = dbo.Inventory.NAMEKEY 
WHERE (dbo.InvHeader.OMVoid = 0) AND (dbo.InvHeader.OMDate 
BETWEEN '12/01/2006' AND '12/29/2006 23:59:59') 
GROUP BY dbo.InvDetail.OMItemKey, dbo.InvDetail.OMItemDesc, dbo.Inventory.PRODUCT 
ORDER BY SUM(OMItemQty) DESC
[green]-- Close Parenthesis removed[/green]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Esoteric]

Thanks, Genius George

 

[monksnake]

I came across this

http://www.thescripts.com/forum/thread80082.html

<.

 

[gmmastros]

monksnake,

In this case, esoteric MUST have an order by because he is selecting the top 25 records. Top, without an order by, doesn't make sense. In this case, it would certainly cause the wrong records to get inserted in to the temp table.

Of course, there are a couple other issues with this procedure, like... What if there is a 'tie' for the last records added to the table. SQL Server will arbitrarily pick which of the ties are inserted in to the temp table. In my opinion, this way be one of those cases where with ties would probably make sense.

Also, I don't like that way the date range is selected. The between operator is rarely a good choice when dealing with dates. But that's a topic for another thread.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Thanks for setting me straight gmmastros ))))

<.