SQL Server : select max year

Question

I have a fairly large table of data with multiple years worth of accounts with in it. I'm trying to write a query that will simple pull back the latest year.

However I have tried a number of queries, some from this site that seem to work for other but I can not get it to work on my data set.

The table data looks like so:

So in this example I need to bring back the 2018 year only. The query that I thought might work was:

SELECT *
FROM dbo.Accounts A     
INNER JOIN  
    (SELECT [Account No], MAX(Year) AS MaxYear 
     FROM dbo.Accounts
     GROUP BY [Account No]) MaxYear ON A.[Account No] = MaxYear.[Account No] 
                                    AND A.Year = MaxYear.MaxYear

However this still provides me three records on the Max part when I look for the example account number above.

Any help would be much appreciated.

Thanks in advance

PS: The Year datatype is Float and Account No is nvarchar(255)

by `this still provides me three records on the Max part when I look for the example account number above` do you mean 3 records with 2018, 2017, 2016 years, or three records with each row `2018` years? — Teddy, Nov 30 '18 at 17:11

Zaynul Abadin Tuhin · Answer 1 · 2018-11-30T17:28:23.590

1

you can try like below

SELECT A.*
FROM dbo.Accounts A where A.Year= (select max(year) from dbo.Accounts)

use top

select TOP(1) WITH TIES * from dbo.Accounts
order by Year desc

edited Nov 30 '18 at 17:28

answered Nov 30 '18 at 17:09

Zaynul Abadin Tuhin

31,407
5
33
63

1

Yes, both solutions are possible. It depends which is better, so your answer with both solutions is great :) – Christian Müller Nov 30 '18 at 17:15
1

But the second one will only return one row which doesn’t sound right – Joakim Danielson Nov 30 '18 at 17:19
SELECT TOP 1 with ties * from dbo.Accounts order by Year desc – Cato Nov 30 '18 at 17:20

score 1 · Answer 2 · answered Nov 30 '18 at 17:09

1

Then don't group by the account number in the sub-query?

SELECT *
FROM dbo.Accounts A     
INNER JOIN  
    (SELECT MAX(Year) AS MaxYear 
    FROM dbo.Accounts
    ) MaxYear

ON A.Year = MaxYear.MaxYear

By grouping by the account number (and then joining on it), you were getting each account's max year, rather than the entire table's max year.

answered Nov 30 '18 at 17:09

MatBailie

83,401
18
103
137

Thanks but this doesn't work as I have thousands of historical accounts some ending in 2016, 2017, 2018. This misses a huge amount of the data as it will only join on the max year 2018. I need to find the latest year for each account number. – Carlos80 Nov 30 '18 at 17:25
@Carlos80, you should clarify that in your question – Joakim Danielson Nov 30 '18 at 17:27
@Carlos80 ***THAT*** is exactly what you already had. The three records in your screen shot are for ***different*** accounts. – MatBailie Nov 30 '18 at 18:13
MatBailie, thanks, you are correct THAT is what I already have as an original dataset and so in that example you can see we had a record for this account in 2016, 2017 and 2018. This is the same account and so I'm just trying to pull the latest year which would be one row for the 2018 year. – Carlos80 Dec 03 '18 at 12:12
@Carlos80 - No...The three rows all have ***different*** account numbers... You're getting the latest year per account, from three different accounts, which is three different rows... If they're the same account the problem you have is that there are *(at least)* three ***different*** account numbers for the same account, which is completely FUBAR and ***that*** is what needs to be fixed. – MatBailie Dec 03 '18 at 15:54
@carlos80 Hello? – MatBailie Dec 04 '18 at 08:23

score 1 · Answer 3 · answered Nov 30 '18 at 17:09

1

Try below:

SELECT * FROM dbo.Accounts where Year = (Select Max(Year) FROM dbo.Accounts)

answered Nov 30 '18 at 17:09

Sonal Borkar

531
1
6
12

Thanks but this doesn't work as I have thousands of historical accounts some ending in 2016, 2017, 2018. This misses a huge amount of the data as it will only join on the max year 2018. I need to find the latest year for each account number. – Carlos80 Nov 30 '18 at 17:29
@Carlos80 I am not using any Join in the query. Also, all the historical records are in same table, right? and you want to know the account opened in the latest year, right? – Sonal Borkar Nov 30 '18 at 17:34
Hi Sonal, that is correct, apologies I know you are not joining bad wording on my part. When I run this against my table of data I get only get rows with the year 2019 and thats all, so not getting any accounts that were last seen in 2016,2017,2018. I don't just want the max year I want the last year each account was seen. Thanks – Carlos80 Dec 03 '18 at 12:09

score 0 · Answer 4 · answered Nov 30 '18 at 17:41

0

As I understand year is part of the accountNo column meaning only the first part is the real account number and you want max (last) year per account

SELECT AccountNo, max_year
FROM Accounts
JOIN (SELECT substring(accountno, 1, 6) as accno, max(year) max_year
      FROM accounts
      group by accno) as max_acc ON max_acc.accno = substring(accountno, 1, 6) AND year = max_year

answered Nov 30 '18 at 17:41

Joakim Danielson

43,251
5
22
52

Thanks for your comment Joakim, I was hoping this would work as you have got it spot on. Even though the account number seems different they are the same account just with different year reference. However running your code (and it makes complete sense) it still pulls back all three records instead of just the record with the max year (2018) and this was always my original problem. – Carlos80 Dec 03 '18 at 12:28
It works when I run it so my sample data must be incorrect, could you add more sample data that illistrates you problem. This is my test,[db-fiddle]( https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef114851c9f075ae9cbb891f106167e9) – Joakim Danielson Dec 03 '18 at 12:39
@Carlos80, please check my db-fiddle – Joakim Danielson Dec 03 '18 at 14:40

SQL Server : select max year

4 Answers4