what's wrong in the select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what's wrong in the select statement

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-21 : 06:32:50

select name from child order by name
union all
select aname from agenerate order by aname

Malathi Rao

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-21 : 06:36:34

select name from child
union all
select aname from agenerate

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-21 : 06:38:40

quote:
Originally posted by spirit1

select name from child
union all
select aname from agenerate

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

can we use order by clause in set operators

Malathi Rao

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 10:12:57

Select * from
(
select name from child
union all
select aname from agenerate
) T
order by name

Madhivanan

Failing to plan is Planning to fail

sshelper
Posting Yak Master

216 Posts

Posted - 2007-05-21 : 10:30:37

Modifying Madhivanan's code a little, if you want to return the records with the contents of the first table first sorted by name then followed by the contents of the second table also sorted by name, you can do the following:

Select name from
(
select name as name, 1 as seqno from child
union all
select aname as name, 2 as seqno from agenerate
) T
order by seqno, name

SQL Server Helper
http://www.sql-server-helper.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 11:21:13

Good point sshelper

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-05-21 : 13:35:55

quote:
Originally posted by madhivanan

Select * from
(
select name from child
union all
select aname from agenerate
) T
order by name

Whats wrong with:

select name from child
union all
select aname from agenerate
order by name

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-21 : 23:44:43

quote:
Originally posted by PSamsig

Whats wrong with:
select name from child
union all
select aname from agenerate
order by name

Same as what's wrong with any other invalid SQL statement.

What's your question?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 00:25:56

quote:
Originally posted by PSamsig

quote:
Originally posted by madhivanan

Select * from
(
select name from child
union all
select aname from agenerate
) T
order by name

Whats wrong with:
select name from child
union all
select aname from agenerate
order by name

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

Nothing wrong

With that systax, users used to think order by can be applied before union part as well. In order to show them that order by should be used only after combining the resultset, I used Derived table

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-05-22 : 02:12:38

quote:
Originally posted by madhivanan

Nothing wrong

With that systax, users used to think order by can be applied before union part as well. In order to show them that order by should be used only after combining the resultset, I used Derived table

Then what about:

select name from child
union all
select aname from agenerate
order by 1

or is that equally 'ugly' ?

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-05-22 : 02:18:27

quote:
Originally posted by LoztInSpace

Same as what's wrong with any other invalid SQL statement.

What's your question?

It is very valid, the original query was not, you can only place an ORDER BY to the whole query.

The question was simply why Madhivanan made it more complex than nessesery, because he is to smart not to know this.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 02:27:03

quote:
Originally posted by PSamsig

quote:
Originally posted by madhivanan

Nothing wrong

With that systax, users used to think order by can be applied before union part as well. In order to show them that order by should be used only after combining the resultset, I used Derived table

Then what about:
select name from child
union all
select aname from agenerate
order by 1
or is that equally 'ugly' ?

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

There is always more than one way to do the same thing
All the methods suggested by you and me are valid
There is no question on which is valid

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 02:31:13

I believe Madhi's method makes visually more sense and is less confusing.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources