August 19, 2009 at 7:50 am
Dave Ballantyne (8/19/2009)
GSquared (8/19/2009)
Okay, I'll bite. Why is that one surprising?
Just simply because after 10 years+ of solid TSQL work, i'd never needed to do that.
You learn something new everyday 🙂
SOME of us learn something new everyday (myself especially). Some learn nothing new, regardless of the suggestions/tips given to them on a silver platter. Just my 2 cents and/or rant for the day! 😀
-- You can't be late until you show up.
August 19, 2009 at 8:03 am
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!
Well done!
- 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
August 19, 2009 at 8:03 am
Can't find it now, but I read a post earlier this morning where the OP thought he couldn't use an ORDER BY in a query because he was using a UNION ALL.
August 19, 2009 at 8:09 am
Lynn Pettis (8/19/2009)
Can't find it now, but I read a post earlier this morning where the OP thought he couldn't use an ORDER BY in a query because he was using a UNION ALL.
I've seen people try to use Order By in the sub-queries in Union situations, and you can't do that. Probably someone tried that, it didn't work, and he didn't then go on and realize you can do an overall Order By for the whole query.
- 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
August 19, 2009 at 9:09 am
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!
Congrats
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2009 at 9:10 am
GSquared (8/19/2009)
Lynn Pettis (8/19/2009)
Can't find it now, but I read a post earlier this morning where the OP thought he couldn't use an ORDER BY in a query because he was using a UNION ALL.I've seen people try to use Order By in the sub-queries in Union situations, and you can't do that. Probably someone tried that, it didn't work, and he didn't then go on and realize you can do an overall Order By for the whole query.
Yeah, but what if you're trying to do the union of two queries and the results you want from one of the sub-queries depends on the ORBER BY since you're using a TOP in the select.
SELECT TOP 5 *
FROM Table1
ORDER BY Col1 DESC
SELECT *
FROM Table1
WHERE Col12 is NULL (or some value)
Can you join these two queries with a UNION or UNION ALL?
Or is there some other way of doing the WHERE statement that satifies the result set of the top 5 rows ordered by a column (like the date so you can get the most recent entries) and also any entries that haven't been updated from a NULL value in one of the columns?
I've tried to combine those two queries without much luck, finding out that you can't put the ORDER BY clause in the sub-queries. Trying it with the ORDER BY clause with the orderall query didn't give me the results I wanted.
I can post table descriptions and the like in a new thread if you want more information as this might be a bit off topic for The Thread. :w00t:
-- Kit
August 19, 2009 at 9:18 am
Kit G (8/19/2009)
Yeah, but what if you're trying to do the union of two queries and the results you want from one of the sub-queries depends on the ORBER BY since you're using a TOP in the select.SELECT TOP 5 *
FROM Table1
ORDER BY Col1 DESC
SELECT *
FROM Table1
WHERE Col12 is NULL (or some value)
Can you join these two queries with a UNION or UNION ALL?
Completely untested, but maybe...
SELECT * FROM
( SELECT TOP 5 *
FROM Table1
ORDER BY Col1 DESC
) sub
UNION
SELECT *
FROM Table1
WHERE Col12 is NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 9:29 am
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!
Congrats, but of course exactly what I'd have expected for you, barring the one negative comment.;-)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2009 at 9:36 am
GilaMonster (8/19/2009)
Completely untested, but maybe...
It works! Yay! Thanks! Can you direct me to the reference that explains what you did?
-- Kit
August 19, 2009 at 9:38 am
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!
Congrats Gail... Party Time.. 🙂
-Roy
August 19, 2009 at 10:03 am
Jack Corbett (8/19/2009)
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!Congrats, but of course exactly what I'd have expected for you, barring the one negative comment.;-)
Comment essentially said that I need to stop making jokes because they're not funny (odd, people in audience were laughing), that I need to be more confident and stop trying to impress the audience.
Oh well.... Can't please everyone....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 10:03 am
Gail, (or someone else fairly familiar with profiler), here is one that could possibly use a little more detailed assistance.
August 19, 2009 at 10:09 am
Kit G (8/19/2009)
Can you direct me to the reference that explains what you did?
Reference? I'm sure there's one somewhere but I wouldn't know where.
Ok, when there are multiple queries combined with union (and I assume EXCEPT and DIFFERENCE as well), order by is not allowed in the outer-most level of any of the queries, except the last one where it applies to the union as a whole.
Order by is allowed in a subquery, providing there's a top statement in the subquery as well (and preferably a row-limiting top).
Hence, to get an orderby within a union, put the order by into a subquery, where it is allowed and make sure that there's a row-limiting TOP clause.
Does that make the slightest sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 10:13 am
Roy Ernest (8/19/2009)
GilaMonster (8/19/2009)
*Woot* Just got TechEd evaluation scores. Bar one comment, very nice!Congrats Gail... Party Time.. 🙂
Party time next time I see my Lead (probably next week), I'll make sure he pays.
I scored best whiteboard session of the entire conference and best breakout session of the DAT track. :w00t: :w00t:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 10:14 am
Lynn Pettis (8/19/2009)
Gail, (or someone else fairly familiar with profiler), here is one that could possibly use a little more detailed assistance.
Thanks Gail for jumping in on this one.
Viewing 15 posts - 7,486 through 7,500 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply