SQL Server query to join two tables

Question

I've got a 2 part problem where through the use of google managed to find the answer to the first part at SQL get the last date time record

User Osy code work really well for me,

Osy's code below!

 select filename, dates, status 
 from yt a 
 where a.dates = (select max(dates)
                  from yt b
                  where a.filename = b.filename)

The query returns only the latest dates for each filename.

If I could just stick to the same example question as url above.

This is the table used in the example:

yt table:

+---------+------------------------+-------+
|filename |Dates                   |Status |
+---------+------------------------+-------+
|abc.txt  |2012-02-14 12:04:45.397 |Open   |
|abc.txt  |2012-02-14 12:14:20.997 |Closed |
|abc.txt  |2013-02-14 12:20:59.407 |Open   |
|dfg.txt  |2012-02-14 12:14:20.997 |Closed |
|dfg.txt  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

The second part of the problem:

What I am now trying to achieve is that I have a second table and would like to join the results from the query above on the filename and return the user.

Table2

+--------+--------+
|filename |ref     |
+---------+--------+
|abc.txt  |Heating |
|dfg.txt  |Cooling |
+---------+---- ---+

Result that I am trying to achieve from the query is as follows, using Osy's code above to return only the latest for each entry per device, and then to display the ref column and not display the filename

Example:

+---------+------------------------+-------+
|ref      |Dates                   |Status |
+---------+------------------------+-------+
|Heating  |2013-02-14 12:20:59.407 |Open   |
|Cooling  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

I can use a inner join directly on the yt table but cannot get is to combine (nest) with the code from Osy above.

Using SQL Server 2012. Please let me know if I left out anything.

Thank you.

you don't NEED to "nest" the join. just add a join statement to the parent/outer query as you would any other join. — Marc B, Jan 28 '16 at 19:15
What do you mean when you say you "cannot get is to combine (nest) with the code from Osy above."? Did you get an error? Please post your attempt to do this, and what was wrong with the result. — Tab Alleman, Jan 28 '16 at 19:46

score 1 · Answer 1 · answered Jan 28 '16 at 21:14

1

This might be faster way to do it.

SELECT  [ref],
        [dates],
        [status]
 FROM   (SELECT a.[filename],
                a.[dates],
                a.[status],
                t2.[ref],
                ROW_NUMBER() OVER (PARTITION BY a.[filename] ORDER BY a.[dates] DESC) [Rn]
         FROM   yt a
                JOIN Table2 t2 ON a.[filename] = t2.[filename]
        ) t
 WHERE  t.Rn = 1

answered Jan 28 '16 at 21:14

JamieD77

13,796
1
17
27

Your solution works perfectly. Thank you, gives me a different perspective! – Colduldel Feb 03 '16 at 14:39

score 0 · Accepted Answer · answered Jan 28 '16 at 19:22

0

Continuing with your posted example code, again perform one more JOIN with Table2 like

select t2.ref, xx.Dates, xx.Status
from Table2 t2 join (
select filename, dates, status 
 from yt a where a.dates = (
 select max(dates)
 from yt b
 where a.filename = b.filename
)) xx on t2.filename = xx.filename;

answered Jan 28 '16 at 19:22

Rahul

76,197
13
71
125

Thank you Rahul, I managed to test out your query on my tables last night, before the power went out(thanx third world) had to re-do most of my work this morning. Updated code on [SQLFiddle](http://sqlfiddle.com/#!3/93dcb/1) – Colduldel Jan 29 '16 at 04:45

score 0 · Answer 3 · answered Jan 28 '16 at 19:44

0

Speculating the bottom query but is should work:

SELECT a.ref, MAX(b.Dates), b.Status
FROM Table2 AS a INNER JOIN yt AS b ON a.ref=b.filename AND b.Status='Open'
GROUP BY a.ref, b.Status

Let me know if it works

answered Jan 28 '16 at 19:44

Steven de Beer

108
1
14

This will give you 2 records for each `ref` since there are 2 `status` and the max `dates` for each status – JamieD77 Jan 28 '16 at 21:17
Then Change the `INNER` to `RIGHT` – Steven de Beer Jan 28 '16 at 21:49
Dankie Steven, just had a quick go at your query, did not seem to return the result. will have another go at it tomorrow and update my comment. thanks for your time. – Colduldel Feb 03 '16 at 14:42
No Problem. Jamie's solution works but is a deprecated method as of MS Sql Server 2003 when `JOIN` made an appearance. Using `JOINS` is much more robust and flexible. – Steven de Beer Feb 03 '16 at 16:25
Aslo here is a good explenation with examples of `JOINS` [link] (http://blogs.msdn.com/b/craigfr/archive/2006/07/19/671712.aspx) – Steven de Beer Feb 03 '16 at 16:32

SQL Server query to join two tables

3 Answers3