CODEWITHSUNDEEP
sqlsql-serverjoin
Houss_gc
Houss_gc

Reputation: 739

Table inner join itself

I have a table with 3 columns (code, state, date), it records the history of a code state, each code may have changed state multiple times.

I want to show the last state of each code what I did was like this

SELECT code,MAX(date), .... 
FROM table 
GROUP BY code.

I don't know what to put exactly to get the state. I tried to just put state so it gets the state corresponding to the combination of code,max(date) but it gives me the error of not in aggregate function.

thank you in advance for your help.

Upvotes: 0

Views: 378

Answers (4)

xQbert
xQbert

Reputation: 35323

If I understand you have data such as

CODE  State  Date
1     IL     1/1/2016
1     IA     1/1/2017
1     AL     1/1/2015

and you want to see in your results

1 IA 1/1/2017

using a window function and a common table expression (with): we assign a row number to each code based on the date in descending order and return only the first row for each.

With CTE AS (SELECT code
           , date
           , state
           , Row_number() over (partition by code order by date desc) RN
             FROM table )
SELECT Code, Date, State 
FROM CTE 
WHERE RN =1

Using a subquery: (we get the max date for each code and then join back to the base set to limit the rows returned.

SELECT A.code, A.date, A.state
FROM table  A
INNER JOIN (SELECT max(date) mdate, code 
            FROM table 
            GROUP BY code) B
 on A.Code = B.Code
and A.Date = B.MDate

The later query was used when/if window functions are not available. The modern method of solving your question is using the first approach.

In essence what the 1st query does is assign the # 1 to x for each code based on the date descending. So the max date gets a RN of 1 for each code. Thus when we say where RN = 1 we only return codes/states/records having max dates for the code in question. We use a with statement because we need the RN to materialize (actually get generated in memory) so that we can then limit by it in the second part of the with (common table expression) query.

Upvotes: 2

Cherry Blossom Girl
Cherry Blossom Girl

Reputation: 553

Youn can do it with a join to itself

SELECT State,Code,Date
FROM table t
JOIN (
SELECT  Code, MAX(Date) as Date
FROM table
GROUP BY  Code) t1 on t1.Code= t.Code and t.Date=t1.Date

Upvotes: 1

OLIVER.KOO
OLIVER.KOO

Reputation: 5993

If you want to user inner join like you mention in your post Inner join back to itself with matching code and date

SELECT * 
FROM table t1
INNER JOIN  (SELECT code,MAX(date)
             FROM table 
             GROUP BY code) codeWithLatestDate ON t1.code = codeWithLatestDate.code AND t1.date = codeWithLatestDate.dat3

However I would suggest add state to your GROUP BY clause and SELECT cluase

SELECT code,MAX(date),state
FROM table 
GROUP BY code, state

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

If you're doing an aggregate, like MAX(), then all other non-aggregate columns that are in your select, need to also be in your GROUP BY. That's why you're getting the error when you add state to only the select. If you add it to the select and group by it, you'll get your results:

SELECT State, Code, MAX(Date)
FROM table
GROUP BY State, Code

Upvotes: 1

Related Questions