INNER JOIN subquery SELECT - SQL Server Forums

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

Other Forums

MS Access

INNER JOIN subquery SELECT

Author

Topic

Dennisvm82
Starting Member

1 Post

Posted - 2011-07-28 : 09:53:52

SELECT t.*
FROM Deployments t
INNER JOIN
(
SELECT System, Max(ActiveDate) as ActDate
FROM Deployments
WHERE QaEnv = "Delta (UAT2) EAI-V3" AND Type = "Adapter" 
AND (([ActiveDate] Between #6/20/2011# And #6/30/2011#)) 
GROUP BY System
) 
x ON t.system = x.system AND t.ActiveDate = x.ActDate;

Can any of you help me out?

Thanks in advance,
Dennis

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 21:17:00

Add that in a where clause.


...
AND (([ActiveDate] Between #6/20/2011# And #6/30/2011#)) 
GROUP BY System
) 
x ON t.system = x.system AND t.ActiveDate = x.ActDate
where t.QaEnv = "Delta (UAT2) EAI-V3";

Not quite clear to me whether I am misinterpreting the sample data. Alternatively, you could add QaEnv in the inner query (for example as max(QaEnv)) and then join on QaEnv in the outer query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 01:42:07

[code]SELECT t.*
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY system ORDER BY ActiveDate DESC,Configuration ASC) AS rn,* from Deployments) t
WHERE rn=1
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources