Convert query to use JOIN in subquery

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)

Convert query to use JOIN in subquery

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2014-05-15 : 12:17:37

How would I convert this query to use a JOIN in the subquery:

SELECT   ProcDate , MAX(b.DayKey) DayKey 
FROM    dbo.MyTable a ,
        ( SELECT    MAX(DayKey) DayKey
          FROM      dbo.MyTable
          WHERE ExtractType = 'D'
        ) b
WHERE   ( ExtractType = 'D' )
        AND a.Daykey = b.DayKey
        GROUP BY ProcDate

Thanks.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2014-05-15 : 12:50:06

I think I have answered my own question. Anyone let me know if it seems like this is incorrect or I am missing anything. It is supposed to yield a single row:

I Do think I have found the answer to my own question. It does work:
SELECT   a.ProcDate, MAX(b.DayKey) DayKey 
FROM    dbo.MyTable a
INNER JOIN ( SELECT MAX(DayKey) DayKey
          FROM dbo.MyTable
          WHERE ExtractType = 'D'
        ) b
ON a.DayKey = b.DayKey
WHERE   ( a.ExtractType = 'D' )
GROUP BY a.ProcDate

Thank you.

Duane

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-15 : 14:22:51

[code]
;WITH b AS
(
SELECT MAX(DayKey) AS DayKey
FROM dbo.MyTable a
WHERE ExtractType = 'D'
)
SELECT MAX(ProcDate) AS ProcDate, a.DayKey
FROM MyTable a
JOIN b ON a.DayKey = b.DayKey AND a.ExtractType = 'D'
GROUP BY a.DayKey
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-15 : 18:00:49

I think the code below is functionally equivalent, but less overhead and much clearer as to what is going on:



SELECT DISTINCT
           ProcDate, DayKey
FROM       dbo.MyTable a
WHERE ExtractType = 'D' AND
      DayKey = ( SELECT     MAX(DayKey)
                 FROM       dbo.MyTable
                 WHERE ExtractType = 'D'
               )

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2014-05-16 : 14:32:00

Thank you for both of these. I am going to try these and put them into my inventory of possible solutions.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources