Getting the Max ID from multiple Joins

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

SQL Server 2008 Forums

Transact-SQL (2008)

Getting the Max ID from multiple Joins

Author

Topic

marqueluis
Starting Member

2 Posts

Posted - 2013-06-24 : 11:06:49

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

52326 Posts

Posted - 2013-06-24 : 12:53:51

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

marqueluis
Starting Member

2 Posts

Posted - 2013-06-24 : 13:16:56

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 13:34:39

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources