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.
| Author |
Topic |
|
jamilahmed
Starting Member
18 Posts |
 Posted - 2009-03-27 : 07:22:56
|
| Hallo, I have the following query (shown below), which when I run in SQL server orders the results by Oracle_no (this is correct), however, when I open view (right click the query and select open view) it orders it by prac_no. Why is it ordering by prac_no? Please note: The query was originally ordering by prac_no, I have updated it to order by Oracle_no. Any help will be appreciated...SELECT TOP (100) PERCENT gprdsql.TblQuarPay.prac_no, gprdsql.TblQuarPay.prac_eid, gprdsql.TblOracleNos.Oracle_noFROM gprdsql.TblQuarPay LEFT OUTER JOINgprdsql.TblOracleNos ON gprdsql.TblQuarPay.pay_method = gprdsql.TblOracleNos.Pay_method AND gprdsql.TblQuarPay.prac_eid = gprdsql.TblOracleNos.Prac_eid ORDER BY gprdsql.TblOracleNos.Oracle_no |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 07:30:40
|
| You have to look at the view definition. If it has an explicit ORDER BY then you can change it. However, this is not a good idea as it may impact other programs that use the view.Never rely on the natural order of any SQL table or view. If you must have ordered results, always have an ORDER BY clause in your query. |
 |
|
|
jamilahmed
Starting Member
18 Posts |
Posted - 2009-03-27 : 07:38:27
|
| Thanks for the answer. Please could you tell me what to do... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 08:06:10
|
| I just did. Don't open the view from Management Studio if it doesn't provide the order you want, run your query instead. |
|
|
|
jamilahmed
Starting Member
18 Posts |
Posted - 2009-03-27 : 10:46:34
|
| Ok, I run the query. However, I am working on another application in VB.net which is binded to that query and when the data is extracted in the front end it brings it & sorts it out in prac_no and it's really annoying. It needs to be arranged by oracle_no..I tried to delete the prac_no in the query but still it brings it arranged in prac_no though it is not displayed anymore. I tried to copy the view and created similar view with same query. Same results.. Really getting to my nerves...Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-27 : 11:19:51
|
| You can change the statement your VB app uses to include an ORDER BY clause, it's not difficult at all. |
|
|
|
jamilahmed
Starting Member
18 Posts |
Posted - 2009-03-27 : 11:47:25
|
| Thank you very much, done it |
|
|
|
|
|
|
|
|
Open View