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

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:

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

---

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:

---

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:

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

---

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

I'm using SQL Server 2008, by the way. Thanks in advance...

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

Code:

---

;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

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

Bah!

Quote:

---

Originally Posted by VBForums

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