CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-byaggregate-functions
Sandy DeLeon
Sandy DeLeon

Reputation: 662

Aggregate Functions To Pull More Record Field Data

I would like to know what would be the best way to get the data from a specific row when I use a Group By query. The real query is more complex than the example I'm providing here so I'm looking for something other than a sub-select on the Sales table.

I'm using MSSQL 2008 and I would like something that allow me to get the date field from the Sales record that has the max(amount).

Query

select uid, firstName, lastName, AmountFromTagetedRow, DateFromTargetedRow,
from users u inner join 
     sales s on u.uid = s.custID
group by uid, firstName, lastName
order by uid

USERS

uid  firstName  lastName
1    Sam        Smith
2    Joe        Brown
3    Kim        Young

SALES

sid  Custid  date        amount ...
1    1       2016-01-02  100
2    3       2016-01-12  485
3    1       2016-01-22  152
4    2       2016-02-01  156
5    1       2016-02-02  12
6    1       2016-03-05  84
7    2       2016-03-10  68

RESULTS

uid  firstName  LastName  amount  date
1    Sam        Smith     152     2016-01-22
2    Joe        Brown     156     2016-02-01
3    Kim        Young     485     2016-01-12

Upvotes: 0

Views: 31

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Your posted query doesn't match your amount but something like this should get you pointed in the right direction.

with SortedResults as
(
    select uid
        , firstName
        , lastName
        , AmountFromTagetedRow
        , DateFromTargetedRow
        , ROW_NUMBER() over (partition by u.uid order by AmountFromTagetedRow desc) as RowNum
    from users u inner join 
         sales s on u.uid = s.custID
    group by uid
        , firstName
        , lastName
)

select *
from SortedResults
where RowNum = 1
order by uid

Upvotes: 0

Related Questions