SQL Query MAX date and some fields from other table

Question

I have two tables, say A and B.

Table : A

ID_Sender |  Date     
________________________
   1      | 11-13-2013
   1      | 11-12-2013

   2      | 11-12-2013
   2      | 11-11-2013

   3      | 11-13-2013
   4      | 11-11-2013

Table : B

    ID    |  Tags
_______________________
1         | Company A
2         | Company A
3         | Company C
4         | Company D

result table:

Tags        |   Date
____________________________
Company A   | 11-13-2013
Company C   | 11-13-2013
Company D   | 11-11-2013

I have already tried out this out GROUP BY with MAX(DATE) but failed with no luck, I did some inner joins and subqueries but failed to produce the output.

Here is my code so far, and an image for the output attached.

SELECT E.Tags, D.[Date] FROM 
        (SELECT A.ID_Sender AS Sendah, MAX(A.[Date]) AS Datee
        FROM tblA A
        LEFT JOIN tblB B ON A.ID_Sender = B.ID
        GROUP BY A.ID_Sender) C
    INNER JOIN tblA D ON D.ID_Sender = C.Sendah AND D.[Date] = C.Datee
    INNER JOIN tblB E ON E.ID = D.ID_Sender

Any suggestions? I'm already pulling my hairs out ! (maybe you guys can just give me some sql concepts that can be helpful, the answer is not that necessary cos I really really wanted to solve it on my own :) )

Thanks!

Ooops, I can't add an image yet it needs some reputation thingy before I can do so — Codemunkeee, Nov 13 '13 at 08:42
I have it stored locally on the computer, I think I need to upload it on a different site before i can use it/ — Codemunkeee, Nov 13 '13 at 08:51

score 5 · Accepted Answer · answered Nov 13 '13 at 08:48

5

SELECT Tags, MAX(Date) AS [Date]
FROM dbo.B INNER JOIN dbo.A
  ON B.ID = A.ID_Sender
GROUP BY B.Tags

Demo

The result

Company A   November, 13 2013 00:00:00+0000
Company C   November, 13 2013 00:00:00+0000
Company D   November, 11 2013 00:00:00+0000

answered Nov 13 '13 at 08:48

Tim Schmelter

450,073
74
686
939

3

you have given my answer .. i was writing same query but befor submitting when i clicked load answer .. your answer was there .. so upvoted this – Dhaval Nov 13 '13 at 08:50
it says, Invalid object name 'dbo.B'. – Codemunkeee Nov 13 '13 at 08:55
@Codemunkeee: That's your table name(_"I have two tables, say A and B"_), `dbo` is the schema. – Tim Schmelter Nov 13 '13 at 08:57
@tim What the! I feel so stupid now.. all that coding that I did for almost 3 hours is just a 4 line code.. :D hehe thank you so much! Now I can finally move on with my life. :D – Codemunkeee Nov 13 '13 at 08:58
@tim yup I thought you were referring to the databases there.. hehe. thanks! – Codemunkeee Nov 13 '13 at 08:59

score 0 · Answer 2 · answered Nov 13 '13 at 08:59

try this please let me correct if I wrong. In table B Id = 2 is Company B I am assuming.. if it is right then go ahead with this code.

   declare @table1 table(ID_Sender int,  Dates varchar(20))
   insert  into @table1 values 
   ( 1      , '11-13-2013'),
   (1      , '11-12-2013'),
   (2      ,'11-12-2013'),
   (2      ,'11-11-2013'),
   (3      ,'11-13-2013'),
   (4      ,'11-11-2013')

   declare @table2 table ( id int, tags varchar(20))
   insert into @table2 values 
   (1         ,'Company A'),
   (2         , 'Company B'),
   (3         , 'Company C'),
   (4         , 'Company D')


;with cte as
(
    select 
    t1.ID_Sender, t1.Dates, t2.tags
    from @table1 t1
    join
    @table2 t2 on t1.ID_Sender = t2.id
)
select tags, MAX(dates) as dates from cte group by tags

score 0 · Answer 3 · edited May 27 '15 at 04:33

0

Change first your schema with 'Company B' on ID in B Table

Here's my code:

 Select B.Tags, max(A.Date) as 'Date'
 from A, B
  where B.ID = A.ID_Sender
  group by B.Tags

edited May 27 '15 at 04:33

Teja Nandamuri

11,045
6
57
109

answered May 27 '15 at 04:05

Max

1

SQL Query MAX date and some fields from other table

3 Answers3