How to get the max of 2 joined Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the max of 2 joined Tables

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-04-27 : 06:02:59

Hi,

I have 2 tables; table A and table B.In each of them there are same number of rows. (lets say, 2 rows in each of the tables)

The columns of Table A are:
ID - identity auto increment
NAME
ODD

The columns of Table B are:
ID - identity auto increment
EVENT
STATUS

I would like to join the highest ID rows of two tables.

Best Regards.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-27 : 06:42:28

So, your data has NO RELATION at all? Why not keep it in notepad instead?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-04-27 : 08:36:06

Yes actually there is a foreign key which is NEVID in both tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:14:54

do you mean this?


SELECT a.NEVID,a.NAME,a.ODD,b.EVENT,b.STATUS
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY NEVID ORDER BY ID DESC) AS Rn,*
FROM TableA
)a
INNER JOIN
(SELECT ROW_NUMBER() OVER (PARTITION BY NEVID ORDER BY ID DESC) AS Rn,*
FROM TableA
)b
ON b.Rn = a.Rn
AND b.NEVID = a.NEVID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-04-28 : 04:19:25

Hi visakh16,

Thanks for your reply. I will let you know after I try it. By the way are there any performance issue for your query?

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 06:13:29

Here is a query using a small workaround to the problem.
Hope this works:


--Creating Tables

Create Table Test
(Id int Identity(1,1),
 Status varchar(20) )
 
Create Table Test1
(Id int Identity(1,1),
 Status varchar(20) )
 
 
--Inserting Data into Tables

Declare @temp int = 1
While(@temp <= 10)
Begin
Insert Into Test Values('Checked')
Insert Into Test1 Values('Unchecked')
Set @temp = @temp + 1
End


--Query for your requirement

Select Top 1 a.*, b.* From Test As a, Test1 As b
Where a.Id = (Select Max(Id) From Test1)
Order By b.Id Desc

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-28 : 18:58:49

quote:
Originally posted by raysefo

Hi visakh16,

Thanks for your reply. I will let you know after I try it. By the way are there any performance issue for your query?

why dont you test and check it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-04-30 : 03:12:25

Hi visakh16,

It is pretty fast. One more question related to this query. If I want to get the top 2 rows from both tables, how would I do it?

best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 09:53:20

quote:
Originally posted by raysefo

Hi visakh16,

It is pretty fast. One more question related to this query. If I want to get the top 2 rows from both tables, how would I do it?

best Regards.

simply top 2 rows doesnt make sense in sql table as there's no predefined order. so you've to tell on what basis you want top 2. there should be a unique valued column to determine the order

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-05-09 : 02:17:21

Thank you visakh16, it works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 16:15:48

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources