CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
Angela
Angela

Reputation: 47

How to select the most recent data from SQL joining 3 tables?

I have 3 tables. I need to join the (AGENT table) from (DATA table) by column RANKDATA. There may be multiple entries in the DATA table. I need to select the most recent based on the DATE, then get the CODE_ID and join in CODE table.

problem

Here's my code. I tried to use Max(D.DATE) but I got an error. My only problem, I don't know how to group it by the most recent date.

select A.ID, A.NAME, C.CODE_NAME, D.DATE
from Agent A
JOIN Data D ON A.RANKDATA = D.RANKDATA
JOIN CODE C ON D.CODE_ID = C.CODE_ID

output

Upvotes: 0

Views: 159

Answers (2)

Nayak
Nayak

Reputation: 473

You could try using a CROSS APPLY, as an additional option.

SELECT A.ID, A.NAME, T.CODE_NAME, T.DATE
FROM Agent A
CROSS APPLY
    (SELECT TOP 1 C.CODE_NAME, D.DATE
        FROM
            Data D JOIN CODE C ON D.CODE_ID = C.CODE_ID
            WHERE A.RANKDATA = D.RANKDATA 
            ORDER BY D.DATE DESC) T

An image helps to visualize. But if you need time from others to help you, it would definitely be useful if you include a simple script to create your test data: Something like this:

CREATE TABLE Agent (ID INT, NAME VARCHAR(30) ,RANKDATA INT)
INSERT INTO Agent
VALUES (1,'Mark',12), (2,'Joe',13), (3,'Steve',11), (4,'Sam',10)

CREATE TABLE DATA (ID int, RANKDATA int, CODE_ID VARCHAR(2), DATE datetime)
INSERT INTO DATA
VALUES (1,12,'01','20170901 2:30'), (2,13,'02','20170901 6:30'), 
        (3,11,'03','20170901 4:30'), (4,10,'02','20170901 1:30'), 
        (5,10,'03','20170901 2:50'), (6,12,'02','20170901 5:30')

CREATE TABLE CODE(ID int, CODE_ID varchar(2), CODE_NAME Varchar(15))
INSERT INTO CODE
VALUES (1,'01','RANK 1'), (2,'02','RANK 2'), (3,'03','RANK 3')

Upvotes: 0

Xedni
Xedni

Reputation: 4695

I'm not quite clear on what your results are supposed to be from your image, bu t an alternative to doing this with a group by might be to use row_number. row_number basically projects a column with an incrementing integer (1,2,3,...n) over your rows. You can "partition" the function in the same way you would a group by. In this case, I partitioned it by a.RankData, but that was sort of a guess at what you wanted. The order by defines in what order the row numbering will appear. In this case, I've ordered it by d.[date] descending so the most recent date for every a.RankData will be the number 1. Finally, I turn all that into a subquery so I can put the new column in a where clause and just skim off those where RID = 1 (i.e the date is the max date).

select *
from (select 
          RID = row_number() over(partition by a.RankData order by d.[date] desc),
          A.ID, 
          A.NAME, 
          C.CODE_NAME, 
          D.DATE
      from Agent A
      inner join [Data] D 
          on A.RANKDATA = D.RANKDATA
      inner join CODE C 
          on D.CODE_ID = C.CODE_ID) a
where a.RID = 1

Upvotes: 0

Related Questions