sorting puzzel
-
I have a sql query. When I run the query it brings me resultset unsorted. But when I convert this query in a stor procedure then it brings resultset in sorted order, I do not use any sort order either in query or in stor proc. I want result set as in query unsorted. How to do this?
Thank you in advance.
Bharat.
-
What do you mean unsorted?
If the query has no order by in it, then the server will render the results in the best / fastest way he knows how. If that means the use of an index, then that's how he's gonna read, then send the results.
If you want random results as in different sort order on each run then you can do this :
SELECT .... ORDER BY NEWID()
-
Thank you for the reply.
I have index on the table. But when I run the sotr proc as query it does not order in any particular way but when I run it as stored procedure it orders by description in alphabetical order. I do not want this alphabetical order.
Bharat.
-
What problem are you having in getting those results ordered?
-
Those results are to be displayed unsorted on reporting service's reports..client wants that way.
Thank you.
-
1 - DROP the index
OR
2 - ORDER BY NEWID()
-
Thank you for the hint. I will try out.
Bharat.
-
I'm just curious... why would they want that??
-
I don't know the client, but since I write SQL queries all day long to be used in reports, I think I might have an answer... The Client just wants to make the developer's life hell. That's all it is. I -- in just the last two weeks -- have rewritten the same report, bringing back the same data in 30 different ways. Show me this store with only the product purchased on a Tuesday in the past year. Or, show me the returned product by quantity, but separate it out by store, date and value. I've decided that Clients are just crazy and they want everyone else to be crazy too.
-
Well you are right. In my case I have to confirm to the way they used to see the reprots and which was written by ealier developers using Web Grid control and hence they used to sort by some other logic. I changed the way they were accessing data from sql server and filling the Grid. I use reporting services Report Viewer Control to populate the data.
Thanks.
Bharat.
-
"...rewritten the same report, bringing back the same data in 30 different ways..."
Sounds like a good application of Pivot Tables. You can write the base SP, and invoke it from an Excel Pivot table, then the user can sort & re-sort all day long without bothering you.
-
Pivot tables would be a good idea, if the client was willing to make their own pivot tables!
They just want it how they want it and when they want it. It's our largest client, so we just do as they ask! -
Thanks for ideas. I use asp.net application which has pages with ReportViewer Controls on it, these pages display reports of reporting service's reports and I have to follow client's wishes(Client is the king and pays for bread and butter!!).I can not initiate client to use anything else. as they have their specifications.
Bharat.
-
It would appear that Sql Server is using different execution plans for the SP and the query. Chekck the the execution plans to see what SQL Server is doing, then you can modify the SP to match the execution plan being used by the Ad-Hoc Query.
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 -
Hi Jack,
Thank you for the reply. I will surely go through you suggestions till now I never looked at it.
Bharat.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply