Left join with only one row from second table

Question

0.00/5 (No votes)

See more:

Hi, I have two tables and I want select all rows from first table and rows from second table with latest date. Example:

Table1

ID	Some column
1	sfafsda
2	trwe
3	gfdgsd

Table2

ID	FK_Table1	Date
1	1	22.5.2014
2	1	1.2.2015
3	1	1.1.2015
4	2	1.1.2015
5	2	1.1.2013
6	3	1.1.2010

Table which I need:

ID	Table1_ID	Table2_ID	Date
1	1	2	1.2.2015
2	2	4	1.1.2015
3	3	6	1.1.2010

I was trying with this but that solution dont work for me:

SQL

 SELECT Table1.*, tmp.*
 FROM Table1
 LEFT JOIN (
 SELECT s.* FROM Table2 as s ORDER BY s.Date DESC
 ) as tmp ON Table1.ID = tmp.FK_Table1
GROUP BY Table1.ID

There is an error because "ORDER BY" clause -

ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Has anyone an idea to solve this problem (for MSSQL)?

Thanks!

Posted 18-Oct-15 4:30am

vezo11

Updated 18-Oct-15 4:38am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 18-Oct-15 10:42am

The error message is quite obvious.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-10-18T04:41:00

Solution 1

Try this:

SQL

SELECT t1.*, t2.*
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.ID = t2.FK_Table1
ORDER BY t2.Date DESC

In case you want to get only latest date, try this:

SQL

SELECT t1.*, t2.*
FROM Table1 AS t1 LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY FK_Table1 ORDER BY [Date] DESC) AS RowNo
    FROM Table2  
) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo=1

Tested on:

SQL

SET DATEFORMAT dmy;

DECLARE @table1 TABLE (ID INT,  SomeColumn VARCHAR(30))
INSERT INTO @table1 (ID, SomeColumn)
VALUES(1, 'sfafsda'),
(2, 'trwe'),
(3, 'gfdgsd')

DECLARE @table2 TABLE(ID INT, FK_Table1 INT, [Date] DATE)
INSERT INTO @table2 (ID, FK_Table1, [Date])
VALUES(1, 1, '22/05/2014'),
(2, 1, '01/02/2015'),
(3, 1, '01/01/2015'),
(4, 2, '01/01/2015'),
(5, 2, '01/01/2013'),
(6, 3, '01/01/2010')

SELECT t1.*, t2.*
FROM @table1 AS t1 LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY FK_Table1 ORDER BY [Date] DESC) AS RowNo
    FROM @table2
) AS t2 ON t1.ID = t2.FK_Table1 AND t2.RowNo=1

Posted 18-Oct-15 4:41am

Maciej Los

Updated 18-Oct-15 5:32am

v5

Comments

[no name] 18-Oct-15 10:44am

But this will give a result set with 6 lines... and OP Needs only the latest date I think.
Bruno

Maciej Los 18-Oct-15 10:46am

Thank you, Bruno, for your valuable comment. Seems i missunderstood OP's question. I'll improve my answer soon.

[no name] 18-Oct-15 10:53am

You are welcome. Anyway it seems there is no need of any join to get the result for this :)

Maciej Los 18-Oct-15 10:55am

Yeah... Seems, OP need to "filter" only second table ;)

[no name] 18-Oct-15 11:04am

Anyway a 5 for your effort to help OP. Bruno

Maciej Los 18-Oct-15 11:05am

Thank you, Bruno.

vezo11 18-Oct-15 10:48am

Thanks Maciej for your replay. I have already tried this, but that dont work for me. This query select all rows from the first table and all related rows from the second table.

Maciej Los 18-Oct-15 10:50am

Check updated answer.

vezo11 18-Oct-15 10:57am

Yes I saw. This (updated) query return only one row from the first and second table with highest date. I need all rows from the first table and related row from the second table with highest date. If you look at examples tables in question, your query return one row: 1 | 2| 2.1.2015

Thanks

Maciej Los 18-Oct-15 11:01am

Replace INNER JOIN with LEFT JOIN and everything should be OK.

vezo11 18-Oct-15 11:11am

I tried with this:
SELECT t1.*, t2.*
FROM Table1 AS t1 INNER JOIN (
SELECT *, ROW_NUMBER() OVER(ORDER BY Date DESC) AS RowNo
FROM Table2
) AS t2 ON t1.ID = t2.FK_Table1
WHERE t2.RowNo=1

But the result is the same as with the LEFT JOIN. Or Im missing something?

Thanks

[no name] 18-Oct-15 11:21am

Why you try to join? All Information is available in table2...

Maciej Los 18-Oct-15 11:32am

Sorry for this. Check solution again.

vezo11 18-Oct-15 12:57pm

Ok, thanks Maciej. The last solution works fine.

Maciej Los 18-Oct-15 13:12pm

You're very welcome.

Jeremiah Jackson 12-Feb-21 12:18pm

Thanks!!! I know its 6 years later, but this helped me.

Maciej Los 12-Feb-21 13:24pm

Glad to hear that :)