[RESOLVED] Display entire row returned by a GROUP BY or OVER

[InvisibleDuncan]

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:

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

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:

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

... which returns this:

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

... but I'm never comfortable with have a "Select Something From (Select Something)" syntax.

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...

[techgnome]

yes... and no...

This is something I run into once in a while too, although it usually involves the row_number() function.... but I feel your pain... unfortunately due to the non-deterministic (I think that's right) characteristic of these methods, they can't be used in a where clause directly... I'm sure you know this...

In a nutshell, you have to generate the results, then select from that using the inner select format you used...

bleh... generally speaking, I don't care for it either...

BUT... you do have something else available to you, the Common Table Expression, or CTE... I use it as a way to isolate inner selects into their own area, and it makes things cleaner to read....

;with OrderRank() as (
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
)
Select *
from OrderRank
Where Rank = 1

-tg

[InvisibleDuncan]

Excellent - thanks, TG. I think I'll adopt that approach.

[InvisibleDuncan]

Bah!

You must spread some Reputation around before giving it to techgnome again..

Looks like TG's been too helpful to me - I can't add to his reputation.