SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Getting the Max ID from multiple Joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marqueluis
Starting Member

2 Posts

Posted - 06/24/2013 :  11:06:49  Show Profile  Reply with Quote
I have the following query:

SELECT Table1.Fieldx,Table4.Fieldy FROM Table1 INNER JOIN Table2 ON Table1.ModelId = Table2.Id INNER JOIN Table3 ON Table2.UserId = table3.UserID INNER JOIN Table4 on Table3.Id = Table4.PersonId
WHERE Table1.Fieldx='xxx' and Table4.Fieldy = 'xxxx' and Table4.Fieldy IS NOT NULL AND Table4.Fieldy<> '' GROUP BY Table1.Fieldx,Table4.Fieldy ORDER BY Table1.Fieldx

This query can produce multiple records for each value of field Table1.Fieldx. in order to get the last record for each value of Table1.Fieldx I need this :

- Obtain the MAX Table2.Id from the JOIN between Table1 and Table2
- Obtain the MAX Table3.Id from the JOIN between Table2 and Table3

How do I Write the query to obtain this ?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/24/2013 :  12:53:51  Show Profile  Reply with Quote
can you show some sample data from tables and then show expected output from them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

marqueluis
Starting Member

2 Posts

Posted - 06/24/2013 :  13:16:56  Show Profile  Reply with Quote
Here is an example of the output for Table1.xxxx = '6703200'
I removed the Table4.Fieldy='xxxx' condition.

Fieldx TableBId TableCId Fieldy
6703200 76025 153102 CALIFORNIA
6703200 76009 153070 FLORIDA

I included the Table2.ID and Table3.ID in the SELECT. In this example, what I would need to extract is just the record WHERE Tableb.ID ='76025'. Of course, before executing the query I don't know the values for Tableb.ID nor Tablec.ID.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/24/2013 :  13:34:39  Show Profile  Reply with Quote

SELECT Fieldx,Fieldy
FROM
(
SELECT Table1.Fieldx,Table4.Fieldy, ROW_NUMBER() OVER (PARTITION BY Table1.Fieldx ORDER BY Table2.id DESC) AS Seq
FROM Table1 INNER JOIN Table2 ON Table1.ModelId = Table2.Id INNER JOIN Table3 ON Table2.UserId = table3.UserID INNER JOIN Table4 on Table3.Id = Table4.PersonId
WHERE Table1.Fieldx='xxx' and Table4.Fieldy = 'xxxx' and Table4.Fieldy IS NOT NULL AND Table4.Fieldy<> ''
)t
WHERE Seq=1
ORDER BY Fieldx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000