Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SELECT FUN Expand / Collapse
Author
Message
Posted Wednesday, April 22, 2009 6:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:00 AM
Points: 182, Visits: 69
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.

Can I get my point back please?




Yeah, me too!
Post #702275
Posted Wednesday, April 22, 2009 6:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 24, 2012 8:10 AM
Points: 2,042, 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
Post #702276
Posted Wednesday, April 22, 2009 7:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #702343
Posted Wednesday, April 22, 2009 9:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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.

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. ;)


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
Post #702479
Posted Wednesday, April 22, 2009 10:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #702530
Posted Wednesday, April 22, 2009 11:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
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.

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. ;)


Good point :)...but shouldn't these Qs be screened for accuracy?
Post #702557
Posted Wednesday, April 22, 2009 12:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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.
Post #702591
Posted Wednesday, April 22, 2009 12:19 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #702608
Posted Wednesday, April 22, 2009 12:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:42 AM
Points: 284, Visits: 1,072
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
Post #702640
Posted Thursday, April 23, 2009 4:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:47 AM
Points: 488, Visits: 404
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.
Post #703005
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse