CODEWITHSUNDEEP
sqldatemax
Codemunkeee
Codemunkeee

Reputation: 1613

SQL Query MAX date and some fields from other table

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!

Upvotes: 2

Views: 5522

Answers (3)

Max
Max

Reputation: 1

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

Upvotes: 0

The Hill Boy
The Hill Boy

Reputation: 162

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

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

Upvotes: 5

Related Questions