May 15, 2014 at 10:14 am
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
May 15, 2014 at 10:31 am
dcwilson2009 (5/15/2014)
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
Not sure what the question is. Do you need the subquery to join to something else? Or is the question how can you use a join instead of the subquery?
From what you posted I don't understand the need for a subquery here at all.
SELECT ProcDate, MAX(DayKey) as DayKey
FROM dbo.MyTable a
WHERE ExtractType = 'D'
GROUP BY ProcDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 10:47 am
Thanks for the reply. My query gives a single row. Yours gives me multiple rows. But my query has 2 objects in the FROM clause instead of a JOIN, joining the results from the subquery to the rest of it.
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
Let me know if you think this would be correct in general.
May 15, 2014 at 12:16 pm
So you only want to get the ProcDate and DayKey for the row with the highest DayKey?
How about this?
SELECT top 1 ProcDate, DayKey
FROM dbo.MyTable a
WHERE ExtractType = 'D'
order by DayKey desc
It is difficult to say if this is correct because we haven't seen and ddl, sample data or desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 12:18 pm
dcwilson2009 (5/15/2014)
Thanks for the reply. My query gives a single row. Yours gives me multiple rows.
You only get one row because there is only one row with the max DayKey value. If you have two rows with the same DayKey value you would get two rows in your output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 12:36 pm
Good point, Sean. I am going to have to think about that one. I don't think there would be 2 or more proc dates with the same DayKey, but I do have to think about it. It would probably have to be counted as a failure, rather than processed for that, though. I probably should have provided some DDL, etc. At first, I was just trying to find the old and new syntax.
Thank you.
May 16, 2014 at 12:52 pm
dcwilson2009 (5/16/2014)
Good point, Sean. I am going to have to think about that one. I don't think there would be 2 or more proc dates with the same DayKey, but I do have to think about it. It would probably have to be counted as a failure, rather than processed for that, though. I probably should have provided some DDL, etc. At first, I was just trying to find the old and new syntax.Thank you.
Doesn't the last query I posted using top 1 produce the same output? That would be better because it only has to hit the table once, and it is way easier to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2014 at 9:00 am
Yes, it does, Sean. Thank you. I didn't thing of using TOP 1.
May 20, 2014 at 9:05 am
dcwilson2009 (5/20/2014)
Yes, it does, Sean. Thank you. I didn't thing of using TOP 1.
Cool glad that works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply