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.
| Author |
Topic |
|
Adam West
Constraint Violating Yak Guru
261 Posts |
 Posted - 2009-11-03 : 16:42:34
|
| This SQL works but now i need to show only the top 5. Only want the top 5 items (total purchases, not quantity) of all the items purchasedSELECT t3.CUSTNMBR, SUM(t2.XTNDPRCE) AS XTNDPRCE, SUM(t2.QUANTITY) AS QUANTITY, DATEADD(month, DATEDIFF(month, 0, t1.DOCDATE), 0) AS ID, t6.UserCatLongDescr, t5.ITEMNMBR, t5.ITEMDESC, AVG(t3.BudgetAmount) AS BudgetFROM dbo.SOP10100 AS t1 INNER JOIN dbo.SOP10200 AS t2 ON t1.SOPNUMBE = t2.SOPNUMBE INNER JOIN dbo.CustomerBudget AS t3 ON t1.CUSTNMBR = t3.CUSTNMBR LEFT OUTER JOIN dbo.SOP10106 AS t4 ON t1.SOPTYPE = t4.SOPTYPE AND t1.SOPNUMBE = t4.SOPNUMBE INNER JOIN dbo.IV00101 AS t5 ON t2.ITEMNMBR = t5.ITEMNMBR INNER JOIN dbo.IV40600 AS t6 ON t5.ITMGEDSC = t6.USCATVAL AND t3.USCATVAL = t6.USCATVAL INNER JOIN dbo.RM00101 AS t7 ON t7.CUSTNMBR = t1.CUSTNMBRWHERE t1.DOCDATE BETWEEN ''' + @FromDate + ''' AND ''' + @ToDate + '''' |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-03 : 17:09:24
|
| SELECT TOP 5<body>GROUP BY t3.Custnmber,DATEADD(month, DATEDIFF(month, 0, t1.DOCDATE), 0)ORDER BY Quantity DESCJimEveryday I learn something that somebody else already knew |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-11-03 : 17:11:52
|
| Oops, missed this. I'm surprised your query worked at all without the group by clause, or was that a copy and paste error?GROUP BY t3.Custnmber,DATEADD(month, DATEDIFF(month, 0, t1.DOCDATE), 0),t6.UserCatLongDescr, t5.ITEMNMBR, t5.ITEMDESC JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|
|
Can I get a 'TOP 5' out of this SQL?