Fhd.ashraf Fhd.ashraf -4 years ago 50
SQL Question

Joining and Grouping data from 3 tables

I have two tables

Category

CategorySerno | CategoryName
1 One
2 Two
3 Three


Status

StatusSerno | Status
1 Active
2 Pending


Data

CatId |Status | Date
1 1 2014-07-26 11:30:09.693
2 2 2014-07-25 17:30:09.693
1 1 2014-07-25 17:30:09.693
1 2 2014-07-25 17:30:09.693


When I join them I get I need the Joining of the latest Date/
Like

One Active 2014-07-26 11:30:09.693
Two Inactive 2014-07-25 17:30:09.693
Three Null Null


When I am doing a Join and group them It gives me

One Active 2014-07-26 11:30:09.693
One Active 2014-07-26 11:30:09.693
One Active 2014-07-26 11:30:09.693
Two Inactive 2014-07-25 17:30:09.693
Three Null Null

Answer Source

You could use ROW_NUMBER in a CTE:

WITH CTE AS
(
    SELECT c.CategoryName, 
           s.Status,
           d.Date,
           dateNum = ROW_NUMBER() OVER (PARTITION BY CatId, d.Status 
                                        ORDER BY Date DESC)
    FROM Category c 
    LEFT OUTER JOIN Data d
        ON c.CategorySerno = d.CatId 
    LEFT OUTER JOIN Status s
        ON d.Status = s.StatusSerno 
)
SELECT CategoryName, Status, Date
FROM CTE
WHERE dateNum = 1

Demo-Fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download