SQL SERVER QUERY to select max value record per item

Question

This is the sample table

What I need to achieve is to get or display only the record of tenant with the highest month value. If ever month is equal, I need to base on the latest date value. Here is the sample desired output

With this, I started by this code using max function and incorporated temp table, but unable to get the desired result.

select tenant, name,  date, month
into #sample
from tenant


select * 
from  #sample  
where months = (select max(months)from #sample)

and output to something like this. As I believe, the code is getting the max value in the whole list not considering per tenant filtering.

Any help will be greatly appreciated :)

score 8 · Accepted Answer · answered Oct 07 '15 at 13:13

8

This can be done with the row_number window function:

select tenant, name, date, months
  from (select t.*,
               row_number() over (partition by t.tenant, t.name order by t.months desc, t.date desc) as rn
          from TableName t) x
 where rn = 1

answered Oct 07 '15 at 13:13

sstan

35,425
6
48
66

This worked. Can I just ask, does rn always return to 1? – rickyProgrammer Oct 07 '15 at 13:31
3

For every distinct set of `tenant/name` (`partition by` clause), the `row_number` function will sequentially number the rows `1,2,3,4,...`, *always starting with `1`*, using the `order by` clause to determine the sequence. So, because the `order by` clause is set to `t.months desc, t.date desc`, for every `tenant/name` combination, the row with the greatest `months` value (and latest `date` if more than one row has the same `months` value) will have an `rn` value of `1`. – sstan Oct 07 '15 at 13:37
To better understand this, try running the subquery on its own so that you can see the `rn` values for all the rows. – sstan Oct 07 '15 at 13:38

score 5 · Answer 2 · answered Oct 07 '15 at 13:15

5

You can use a row_number function.

Query

;with cte as 
(
    select rn = row_number() over 
    (
        partition by tenant
        order by months desc,[date] desc
    ),*
    from table_name
)
select tenant,name,[date],months from cte
where rn = 1;

answered Oct 07 '15 at 13:15

Ullas

11,450
4
33
50

Is there any advantage of using the Common Table Expression over the subquery (as done in the other answer)? – pseudocoder Oct 07 '15 at 13:17
2

A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures. http://stackoverflow.com/questions/706972/difference-between-cte-and-subquery – A_Sk Oct 07 '15 at 13:19
@Ajmot OK thanks, but just to be clear, there is no need for recursion in this particular application, right? – pseudocoder Oct 07 '15 at 13:25
another difference CTE can be used several times as different table substitutes, sub-query can only be used once – t-clausen.dk Oct 07 '15 at 13:36
@t-clausen.dk OK, I don't think we need that feature in this example either...that was my point. I guess my question should have been more clear: Is there any reason to use CTE over subquery **in this particular case**? – pseudocoder Oct 07 '15 at 13:44
4

I don't see any improvement in using a CTE in this example in terms of logic or execution; however, IMO, CTEs make queries easier to read than inline views, so I generally prefer to use CTEs. – Michael L. Oct 07 '15 at 14:35

SQL SERVER QUERY to select max value record per item

2 Answers2