CODEWITHSUNDEEP
sqlsql-server
Davor Zubak
Davor Zubak

Reputation: 4746

How to select newest record from two tables SQL?

How to select newest record from two tables using SQL?

"select * from Table1,Table2 WHERE Date=(SELECT MAX(Date) FROM Table1,Table2)"

 -----------    -------------
|   table1  |  |   table2    |
 -----------    -------------
 -----------    -------------
|   title   |  |   title     |
 -----------    -------------
|   text    |  |   text      |
 -----------    -------------
|   date    |  |   date      |
 -----------    -------------

Upvotes: 0

Views: 5284

Answers (3)

Kamil
Kamil

Reputation: 2860

SELECT * FTOM Table1, Tble2... creates a cross join (cartesian product of two sets of records) so ther will be multiple records with the same date. You have to specify more criteria to get only one record, and probably use some join. If you want to choose one record from two tables, where for example Table1 has the newer record than Table2, I think it will be good idea to use union, e.g. 

SELECT col1, col2, col3, col4, ..., coln max(Date) FROM (
    SELECT * FROM Table1 UNION
    SELECT * FROM Table2
) GROUP BY col1, col2, col3, col4, ..., coln
ORDER BY Date;

Upvotes: 1

XIVSolutions
XIVSolutions

Reputation: 4502

This will do it:

SELECT TOP 1 * FROM 
(
    SELECT * FROM Table_1
    UNION ALL
    SELECT * FROM Table_2
) 
AS ALL_RECORDS
ORDER BY Date DESC

Upvotes: 6

Andreas Rohde
Andreas Rohde

Reputation: 609

Try something like:

with tmp(title, text, date) as
(
select title, text, date from table1
union 
select title, text, date from table2
)
select top 1 * from tmp
order by date desc

This should solve your problem.

Upvotes: 4

Related Questions