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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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