SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT FUN


SELECT FUN

Author
Message
Jayeff
Jayeff
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 84
mike (4/22/2009)
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. Crying

Can I get my point back please?




Yeah, me too!
beezell
beezell
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 499
From Microsoft: "The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause."

http://msdn.microsoft.com/en-us/library/ms191141(SQL.90).aspx

This is for SQL Server 2005. I'm assuming it applies to other versions as well. It appears that behind the scenes SQL Server does sort it as part of the duplicate elimination process. This will not be done with UNION ALL though.

For the record: An ORDER BY should be used as part of best practices.

Cheers,
Brian
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5749 Visits: 1619
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 2

or

select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1

gives you same result.

NULL 1
NULL manoj

I tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.

SQL DBA.
Ronald H
Ronald H
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 630
Jayeff (4/22/2009)
mike (4/22/2009)
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. Crying

Can I get my point back please?




Yeah, me too!

And see, you both got you point just by posting that you want your point back. Wink

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
StarNamer
StarNamer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2780 Visits: 1992
SanjayAttray (4/22/2009)
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 2

or

select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1

gives you same result.

NULL 1
NULL manoj

I tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.
The point is that, as documented, the order of results from a union without an explicit ORDER BY cannot be guaranteed. As Hugo pointed out, it is possible under some circumstances to get a different order, hence, in theory, both answers are correct.

Of course, adding 'ORDER BY 2 DESC' forces answer 2. :-)

Derek
Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 416
r.hensbergen (4/22/2009)
Jayeff (4/22/2009)
mike (4/22/2009)
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. Crying

Can I get my point back please?




Yeah, me too!

And see, you both got you point just by posting that you want your point back. Wink


Good point Smile...but shouldn't these Qs be screened for accuracy?
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3638 Visits: 3059
SanjayAttray (4/22/2009)
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 2

or

select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1

gives you same result.

NULL 1
NULL manoj

I tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.


Try actually specifying an "order by" that can be honored by the dbms:
select NULL,'manoj'
union select Null,'1'
union select NULL,'manoj'
order by 2 desc



This returns the same two rows in a different order:
            
----------- -----
NULL manoj
NULL 1

(2 row(s) affected)


So, the difference between the two 2-rows-returned answers comes down to whether you assume the same collation was chosen (or accepted) at installation time. I lost the point because as a mainframe programmer I've got EBCDIC stuck in my head and figured that letters would sort before numbers. But even as I thought that, I was grumbling to myself that without an "order by", it's a crap-shoot between the two.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5749 Visits: 1619
John,

With " order by 2 desc " are you not forcing the query to get result in the way you had desired?

select NULL,'manoj'
union select Null,'1'
union select NULL,'manoj'
order by 2 desc

SQL DBA.
Chris Howarth-536003
Chris Howarth-536003
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 1167
Maybe I'm being pedantic, but I don't think that any of the answers provided are correct.

If you interpret a comma to be a column delimiter, the results should be either:

NULL,manoj
NULL,1

...or:

NULL,1
NULL,manoj

Note that all of the answers in the original question have single quotes around manoj.

Chris
brewmanz
brewmanz
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 406
I got it right (guessed that it would sort alphabetically to remove duplicates, but that's not guaranteed, as mentioned)
However, I reckon that *none* of the answers are (or should that be 'is?) correct.

I would prefer to see
NULL, '1' and NULL,'manoj' (2 rows)
rather than
NULL, 1 and NULL,'manoj' (2 rows)
as the answer, as all the second-field values are clearly stated as character strings.

Some may call that being picky, but hey, two of the 'mutually exclusive' answers are actually the same, according to set theory.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search