May 2, 2012 at 12:05 pm
GSquared (5/2/2012)
The parentheses on Top are only needed outside of Select statements, but whatever is wanted on the standards for that. That's style, not function, so whatever you like on that.
No issues with your other comments, but Books Online has this to say on the TOP parentheses issue:
Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 2, 2012 at 12:07 pm
SQL Kiwi (5/2/2012)
GSquared (5/2/2012)
The parentheses on Top are only needed outside of Select statements, but whatever is wanted on the standards for that. That's style, not function, so whatever you like on that.No issues with your other comments, but Books Online has this to say on the TOP parentheses issue:
Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.
Yep. It's not needed. It's style. Possibly a good idea, but not necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 12:29 am
capn.hector (5/2/2012)
vinu512 (5/1/2012)
This does:
SSelect * From ClientDetailsWhere InterviewDate IN (Select MAX(InterviewDate) From ClientDetails Group By ClientID)
the problem with that query is the IN list contains the max interview date for each client. however if Client A had interviews on '2012-04-20' and '2012-04-30' and Client B Had interviews on '2012-04-01' and '2012-04-20' you would get '2012-04-20' and '2012-04-30' in your IN list giving the correct result for Client B but both interviews for Client A. you need to have the Client ID in the sub query like so (reposted from above):
SELECTcd.ID,
cd.ClientID,
cd.InterviewDate
FROM dbo.ClientDetails AS cd
WHERE
cd.InterviewDate =
(
SELECT MAX(cd2.InterviewDate)
FROM dbo.ClientDetails AS cd2
WHERE
cd2.ClientID = cd.ClientID
);
Yes, you are right Capn.
Didn't think that far. Good one.
May 3, 2012 at 6:34 am
vinu512 (5/3/2012)
capn.hector (5/2/2012)
vinu512 (5/1/2012)
This does:
SSelect * From ClientDetailsWhere InterviewDate IN (Select MAX(InterviewDate) From ClientDetails Group By ClientID)
the problem with that query is the IN list contains the max interview date for each client. however if Client A had interviews on '2012-04-20' and '2012-04-30' and Client B Had interviews on '2012-04-01' and '2012-04-20' you would get '2012-04-20' and '2012-04-30' in your IN list giving the correct result for Client B but both interviews for Client A. you need to have the Client ID in the sub query like so (reposted from above):
SELECTcd.ID,
cd.ClientID,
cd.InterviewDate
FROM dbo.ClientDetails AS cd
WHERE
cd.InterviewDate =
(
SELECT MAX(cd2.InterviewDate)
FROM dbo.ClientDetails AS cd2
WHERE
cd2.ClientID = cd.ClientID
);
Yes, you are right Capn.
Didn't think that far. Good one.
not a problem. when i do these sort of things i try to think of cases where my query would break. i almost posted exactly what you had but then went through the same exercise i posted and realized it needed to be correlated to the outer query.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 3, 2012 at 6:45 am
capn.hector (5/3/2012)
not a problem. when i do these sort of things i try to think of cases where my query would break. i almost posted exactly what you had but then went through the same exercise i posted and realized it needed to be correlated to the outer query.
Great!!...another good lesson learnt...Thats the best part about being here. 😀
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply