Choosing last biggest row by date

Question

I have sorted data by accNumber and Date (3rd coulmn) and grouped it by accNumber and Rest. For each account number there could be many dates (3rd column, sorted from smallest to largest). I want to select rows with highest date (3rd column) for each accNumber. Here is the code for sorting and grouping (from here I want to choose rows with largest date in 3rd columnfor each accNumber):

select a.accNumber, a.Rest, min(a.Date), max(b.Date)
from t1 a, t1  b
where a.Rest=b.Rest
and a.accnumber=b.accnumber
group by a.accNumber, a.Rest 
order by a.accNumber, 3

I am using MS SQL. Thanks in advance

order by a.accNumber, 3 , if your column order changes ,your query will not work properly. — Amir Keshavarz, Oct 25 '13 at 06:33

TechDo · Answer 1 · 2013-10-25T09:08:01.000

0

Please try:

SELECT 
    accNumber,
    Rest,
    Date,
    MaxDate
FROM(
    select 
        accNumber, Rest, Date, 
        MAX(Date) OVER (Partition by accNumber, Rest) MaxDate,
        ROW_NUMBER() OVER(Partition by accNumber order by Date desc) RNum
    from 
        t1
)x
WHERE RNum=1

edited Oct 25 '13 at 09:08

answered Oct 25 '13 at 06:25

TechDo

18,398
3
51
64

Thanks. but it is not something that I want, initially I had 4 columns from two tables, and here there are only three columns from one table. I don't want to change the structure, only choose rows where date is largest for each accNumber. – Eric Klaus Oct 25 '13 at 06:34
Please don't use the implicit-join syntax (comma-separated `FROM` clause), please always explicitly qualify your joins. Among other things, it's harder to lose a join condition that way. – Clockwork-Muse Oct 25 '13 at 08:49
Yeah... u r correct. I didn't notice that, and thought that two are different tables. – TechDo Oct 25 '13 at 09:06

score 0 · Answer 2 · answered Oct 25 '13 at 06:52

looks like you don't need join here, you can do it by window functions:

with cte as (
   select
       a.accNumber, a.Rest,
       min(a.Date) over(partition by a.accNumber, a.Rest) as min_Date,
       max(a.Date) over(partition by a.accNumber, a.Rest) as max_Date,
       row_number() over(partition by a.accNumber order by a.Date desc) as rn
   from t1 as a
)
select accNumber, Rest, min_Date, max_Date
from cte
where rn = 1

A bit hard to write code without test data, but this should do the trick

Choosing last biggest row by date

2 Answers2