I'm wondering if it's possible to do this without resorting to a sub-query. Basically, I have a table with this information on it:
What I would like is to get the entire row that contains the maximum value for each SalesID. I can get it using this statement:Code:OrderID OrderDate CustID SalesID Amount 1 2013-05-01 5 1 5000.00 2 2013-05-02 2 1 3000.00 3 2013-05-03 2 2 2500.00 4 2013-05-04 1 2 8500.00 5 2013-05-04 3 1 7000.00 6 2013-05-05 2 2 1000.00 7 2013-05-06 4 2 1200.00 8 2013-05-06 5 3 6400.00 9 2013-05-07 3 3 3600.00 10 2013-05-09 2 3 2800.00 11 2013-05-09 1 4 1900.00 12 2013-05-10 1 4 7000.00 13 2013-05-10 3 2 2400.00 14 2013-05-11 2 4 3600.00 15 2013-05-12 4 5 2200.00 16 2013-05-12 3 5 3800.00 17 2013-05-12 4 7 9000.00 18 2013-05-13 2 5 2500.00 19 2013-05-13 5 7 1700.00 20 2013-05-14 1 6 1400.00 21 2013-05-14 2 5 4900.00 22 2013-05-15 5 7 4300.00 23 2013-05-15 4 6 6300.00 24 2013-05-15 3 7 7100.00 25 2013-05-16 4 1 2800.00 26 2013-05-17 1 2 400.00
... which returns this:Code:Select * From ( Select OrderID , OrderDate , CustID , SalesID , Max(Amount) Over(Partition By SalesID) As MaxAmount , Rank() Over (Partition By SalesID Order By Amount Desc) as Rank From tblOrder ) O Where Rank = 1
... but I'm never comfortable with have a "Select Something From (Select Something)" syntax.Code:OrderID OrderDate CustID SalesID MaxAmount Rank 5 2013-05-04 3 1 7000.00 1 4 2013-05-04 1 2 8500.00 1 8 2013-05-06 5 3 6400.00 1 12 2013-05-10 1 4 7000.00 1 21 2013-05-14 2 5 4900.00 1 23 2013-05-15 4 6 6300.00 1 17 2013-05-12 4 7 9000.00 1
Is it possible to get the same results without using that syntax? And if not, does it actually matter? I instinctively think that this will have a performance impact, but I may very well be wrong.
I'm using SQL Server 2008, by the way. Thanks in advance...





Reply With Quote