execute stored procedure with order by

Question

execute stored procedure with order by

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

daveriya (11/8/2011)
no.i want to use order by 1,5,do i need to include it in every select statement. i dotn know how to use it,coz every select stm has order by ,and i wanto to display as order by 1,5

So you always want your query sorted by that??? Have you tried putting your order by on your query?

select order 2, book_id,table_name,account_id,account_name

from book,account

union

....

order by order2, account_name

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

daveriya SSCrazy Points: 2352 More actions · Answer 2

no my prob is

select order 2, book_id,table_name,account_id,account_name

from book,account

where book.id = account.id

union all

select order 1,column1,column2

from table1,table2

where-----

union all

select order3,column1,column2

from table1,table2

where-----

union all

select order4,column1,column2

from table1,table2

where-----

in this query,every select has order column,mens if i do order by 1,5,it will dislay result of a select stm who has 1 as order then 2 and so on

where can i put order by for all in one place

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

daveriya (11/8/2011)
no my prob is
select order 2, book_id,table_name,account_id,account_name
from book,account
where book.id = account.id
union all
select order 1,column1,column2
from table1,table2
where-----
union all
select order3,column1,column2
from table1,table2
where-----
union all
select order4,column1,column2
from table1,table2
where-----
in this query,every select has order column,mens if i do order by 1,5,it will dislay result of a select stm who has 1 as order then 2 and so on
where can i put order by for all in one place

You are still ordering the result, you are just using ordinal position instead of the name. Run your query in SSMS and see what the column name are. You can order by them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

daveriya SSCrazy Points: 2352 More actions · Answer 4

i want to order my result by 1,5 ..not with column name

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Again, have you tried?

Someday you will wish you had ordered by column name instead of ordinal position. All you gain by ordinal position is ambiguity.

select SomeColumn from MyTable

union all

select Othercolumn from SomeOtherTable

order by SomeColumn

Aside from being more ambiguous, this is the same thing.

select SomeColumn

union all

select Othercolumn

order by 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

daveriya (11/8/2011)
i want to order my result by 1,5 ..not with column name

Hasn't ordering by column number been deprecated?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

The Dixie Flatline (11/8/2011)
daveriya (11/8/2011)
i want to order my result by 1,5 ..not with column name
Hasn't ordering by column number been deprecated?

I thought so too, but I can't find it in the list of deprecated features.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

GilaMonster (11/8/2011)
The Dixie Flatline (11/8/2011)
daveriya (11/8/2011)
i want to order my result by 1,5 ..not with column name
Hasn't ordering by column number been deprecated?
I thought so too, but I can't find it in the list of deprecated features.

I remember reading it in one of the deprecation lists but it's not there anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 9

daveriya (11/8/2011)
i want to order my result by 1,5 ..not with column name

You just need one final ORDER BY at the bottom.

ORDER BY applies to the *result* of the whole UNION, not just the last query part.

As everyone else has said, use the column name not column number. It's fine.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Jeff Moden (11/8/2011)
GilaMonster (11/8/2011)
The Dixie Flatline (11/8/2011)
daveriya (11/8/2011)
i want to order my result by 1,5 ..not with column name
Hasn't ordering by column number been deprecated?
I thought so too, but I can't find it in the list of deprecated features.
I remember reading it in one of the deprecation lists but it's not there anymore.

Oh good....I was thinking it was deprecated but I couldn't find it either. Glad to know I am not going crazy, or at least no crazier than many of you. :hehe:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

in this query,every select has order column,mens if i do order by 1,5,it will dislay result of a select stm who has 1 as order then 2 and so on
where can i put order by for all in one place

SELECT a.Col1, a.Col2

FROM

(

-- YOUR UNION QUERY

) a

ORDER BY 1,2

Paul White SSC Guru Points: 150467 More actions · Answer 12

Quick example. The following produce the same ordered results, and the output column is named 'Three':

SELECT 3 AS Three

UNION ALL

SELECT 8 AS Eight

UNION ALL

SELECT 1 AS One

UNION ALL

SELECT 5 AS Five

ORDER BY 1

SELECT 3 AS Three

UNION ALL

SELECT 8 AS Eight

UNION ALL

SELECT 1 AS One

UNION ALL

SELECT 5 AS Five

ORDER BY Three

Output:

Three

-----

1

3

5

8

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

daveriya SSCrazy Points: 2352 More actions · Answer 13

thanks a lot DEV,that's what i was looking ,u solve my problem.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Despite the fact that ordering by ordinal is a very bad practice you don't need to use a subquery the way Dev demonstrated. Both Paul and I showed you that is can be done without a subquery. Take heed that ordinal ordering was on the deprecation list at one point. It will become obsolete at some point due to the huge problems it causes people. Personally I would listen to the people on this site and not intentionally do something that is considered very bad practice in the field. Of course it is you that will be debugging your code at 2am when something goes wrong.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Paul White SSC Guru Points: 150467 More actions · Answer 15

Sean Lange (11/9/2011)
Despite the fact that ordering by ordinal is a very bad practice you don't need to use a subquery the way Dev demonstrated. Both Paul and I showed you that is can be done without a subquery. Take heed that ordinal ordering was on the deprecation list at one point. It will become obsolete at some point due to the huge problems it causes people. Personally I would listen to the people on this site and not intentionally do something that is considered very bad practice in the field. Of course it is you that will be debugging your code at 2am when something goes wrong.

I don't know where you found the energy to write and post this, but I applaud you for it. I had given up 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi