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
 General SQL Server Forums
 New to SQL Server Programming
 top for each group

Author  Topic 

gavakie
Posting Yak Master

221 Posts

Posted - 2008-04-15 : 15:33:13
Select Top 15 T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans,
T.Time, T.StoreID, StoreName, C.Name
From Reporting.dbo.QSCHQ_TransactionWithPesoConversion T

Left Join

(Select Time, Sum(Price) as Price, T.StoreID
From Reporting.dbo.QSCHQ_TransactionEntryWithPesoConversion TE
Inner Join Reporting.dbo.QSCHQ_TransactionWithPesoConversion T
on TE.StoreID = T.StoreID
and TE.TransactionNumber = T.RecallID
Where T.Time >= getdate()-365
Group By Time, T.StoreID)ttTemp
on T.Time = ttTemp.Time
and T.StoreID = ttTemp.StoreID

Inner Join QSCHQ.dbo.Cashier C
on T.CashierID = C.[ID]
and T.StoreID = C.StoreID
Inner Join DelSolNet2.dbo.Store S on T.StoreID = S.StoreID
Where T.Time >= getdate()-365

Order By T.Total - T.SalesTax - IsNull( ttTemp.Price,0) desc


So this finds the top 15 transactions but it shows for say all stores I want to show the top 15 transactions for all stores.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 15:35:57
You mean top 15 transactions for each and one store?
If you are using Microsoft SQL Server 2005, have a look at the ROW_NUMBER(), RANK() and DENSE_RANK() functions.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2008-04-15 : 15:37:57
No im using 2000, i need the top 15 transactions for each store, sorry i worded that bad.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 15:47:33
A quick'n'dirty solution can look like this
Select		T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans, 
T.Time,
T.StoreID,
StoreName,
C.Name,
IDENTITY(INT, 1, 1) AS RowID
INTO #Stage
From Reporting.dbo.QSCHQ_TransactionWithPesoConversion T
Left Join (
Select Time,
Sum(Price) as Price,
T.StoreID
From Reporting.dbo.QSCHQ_TransactionEntryWithPesoConversion TE
Inner Join Reporting.dbo.QSCHQ_TransactionWithPesoConversion T on TE.StoreID = T.StoreID
and TE.TransactionNumber = T.RecallID
Where T.Time >= getdate() - 365
Group By Time,
T.StoreID
) ttTemp on T.Time = ttTemp.Time
and T.StoreID = ttTemp.StoreID
Inner Join QSCHQ.dbo.Cashier C on T.CashierID = C.[ID]
and T.StoreID = C.StoreID
Inner Join DelSolNet2.dbo.Store S on T.StoreID = S.StoreID
Where T.Time >= getdate() - 365
Order By T.StoreID,
T.Total - T.SalesTax - IsNull( ttTemp.Price,0) desc

select t.trans,
t.time,
t.storeid,
t.storename,
t.name
from #stage AS t
inner join (
select storeid,
min(rowid) as minrowid
from #stage
group by storeid
) AS x ON x.storeid = t.storeid
where t.rowid between x.minrowid and x.minrowid + 14
order by t.rowid



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2008-04-15 : 16:07:32
Im sorry it doesnt seem to be working, there should in essence be over 1500 rows its only bringing back 170
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:13:49
You have 100 different storeid's?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2008-04-15 : 16:14:42
atleast
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:19:03
With my suggestion above, how many trans records per store do you get?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 2008-04-15 : 16:23:39
anywhere from 1-8
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 16:46:02
I'd say there is something wrong with the original query (which selects into the #stage table).



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 17:01:43
Run the two queries as suggestion above.
The first one by you, and the second one by me.

Add a third query,

SELECT COUNT(DISTINCT StoreID)
FROM #Stage

What result does it return?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -