Moving rows up and down in a table
Question
Friday, October 19, 2007 2:17 PM
Hi all,
I am currently writing a web service that gets data from a MSSQL2000 DB and I need to get the data to create some drop down lists, but these lists are composed of many concatenated elements. To sort these alphabetically is proving to be a royal pain in the butt as they just come out as they are ordered in the DB.
It would be a whole lot easier if i could just reorder the rows in one relevant table. Does anyone know how to do this or if it is possible to move rows up or down?
Thanks a lot!
Richee.
All replies (20)
Monday, October 22, 2007 3:03 AM ✅Answered
I'd like to respond to several incorrect and/or misleading statements in this thread.
Having a PRIMARY KEY (with the default CLUSTERED index) DOES NOT guarantee that the data will be returned in 'sorted' order. WITHOUT an [ORDER BY] clause, IF the server uses multiprocessors to divide up the query, or if the table is partitioned, the data will be returned as the server determines. DO NOT RELY upon a PRIMARY KEY as a de facto ordering mechanism. While it may work now, it could stop working when the next hardware upgrade occurs -and most managers would be quite livid (to say the least) to have to invest resources to fix a problem that could have been easily avoided.
With SQL 2005, a VIEW that includes [...TOP 100 percent ... ORDER BY ...] cannot be relied upon to return the data in the specified order. (It was a 'mistake' that it worked that way with SQL 2000 -and according to the ANSI standards, it shouldn't have.)
You 'could' rearrange the physical order of the data in a table. All you have to do is copy it out to a 'holding' table in the desired order, delete the rows from the existing table, and then add the rows from the holding table back into the production table. (This is the 'simple' explanation.) HOWEVER, again, due to the ANSI standard, there is NO guarantee that SQL Server will provide the data in any desired order WITHOUT an [ORDER BY] clause.
Richee -You wrote that you did not have access to the data server, yet access would be required to attempt to re-organize the data in the table. So do you have access to the data server or not?
From your comments and objects to the suggestions (even if the suggestions were mistaken), your only options may be to sort the data at the web page using java -not a great solution.
Monday, October 22, 2007 4:32 PM ✅Answered
Ekrem,
I stand by what I wrote in my response.
Upon careful reading, you will note I indicated that EVEN having a clustered index is NOT a guarantee that the data will be retrieved in 'sorted' order WITHOUT using an [ORDER BY] clause. And that same statement applies to non-clustered indexes as well.
When you have the opportunity, you may wish to explore some of the anomolies that can occur with SMP (Multiprocessor) servers. And as more and more multicore processors are put 'online', this potential problem should be addressed (and eliminated) in way queries are written.
So, as a definitive statement, the ONLY way to ensure that data is retreived in a 'sorted' order, is to specifically use a [ORDER BY] clause in the query.
Friday, October 19, 2007 2:24 PM
The best thing to do is create a query with just the column in you want and then just GROUP and ORDER by that column.
See my example below:
Code Block
SELECT ColumnName
FROM TableName
GROUP BY ColumnName
ORDER BY ColumnName
I hope that helps. If not let me know.
Regards, Ian.
Friday, October 19, 2007 2:39 PM
Hi Ian,
Thanks for your reply, makes sense, but the problem is that I'm not using SQL to get the data. Its a Java web service that connects to an application server which then gets the data from the DB and passes it back to the web service so using normal SQL queries isnt an option.
The application server just sifts through the DB and presents the data back in the order it comes in the table so if i could just reorder the row it would make things much easier...
Any ideas??
Cheers,
Richee.
Friday, October 19, 2007 6:48 PM
Can you not reference the query from the web service in that case so it is looking at a view of data and not the actual table data?
Saturday, October 20, 2007 7:15 AM
ORDER BY clause can not be used in Views.
You can use a Clusterd Index for the column you want to be sorted? So, you would not do an extra job to sort it again. It'll be sorted automatically.
Saturday, October 20, 2007 9:50 AM
That is an excellent point Ekrem I forgot to add that.
What I was trying to explain was that if a reference was made to a view of the original data then the view can be customised to requirements.
Regards, Ian.
Sunday, October 21, 2007 10:58 AM
Hi guys,
Thanks for your input on this but I can't use anything to do with SQL queries or views, all i can do is use some Java classes supplied for the application server. So its basically like this:
Web service <>Application server<>Firewall<>MS SQL Server
And I dont have any direct access to the DB and the App server only understands Java. I could sort the data in Java if it was a simple list but it isnt. If i could only re-oder the rows in one of the tables then everyting else would fall into place.
Are the rows in a table really completely fixed with no opportunity to move them up or down?
Cheers,
Richee.
Sunday, October 21, 2007 5:55 PM
So you have nothing to do with SQL Server. As this forum section is all about SQL Server, you can not find your answer here.
You say that you can change things on your application only. If this is the only case, I suggest you to visit a forum about Java.
However, you can sort\filter rows using Borland Delphi, Visual Basic .Net or C#. I don't know about Java. The thing I tried to tell is, rows are not "fixed", you can rearrange the them using codes.
Sunday, October 21, 2007 5:58 PM
| Ekrem Önsoy wrote: | |
|
You can ORDER BY in a View if you SELECT TOP 100 PERCENT
Adam
Sunday, October 21, 2007 6:07 PM
| Adam D. Turner wrote: | ||||
|
That is correct. However, not the case in this situation.
Monday, October 22, 2007 4:58 AM
Richee,
Because you cannot access SQL Server, the solution is not SQL Server based.
Because you cannot change the Application Server objects, the solution will not be found there.
My guess is that you receive an "object" and your Java code interprets the properties. One of the properties being a recordset. Then your Web Service processes your Java code.
Unfortunately, you are at the mercy of Java OOP. Fortunately, you have OOP.
As previously mentioned, please post your query in a Java forum.
Adam
Monday, October 22, 2007 7:38 AM
Hi all,
Thanks for your responses. I do have full access to the SQL DB as administrator but I dont have programmatic access to the DB. Naturally I wouldnt have troubled this esteemed forum with a Java question.
Arnie, your solution looks interesting. I will give this a try. Does anyone know if there are any potential repercussions of reordering the rows?
Thanks again,
Richee.
Monday, October 22, 2007 7:42 AM
Arnie,
I would not say "incorrect \ mistaken" because you can make your records sorted using an Index. Saying "incorrect" to this solution, would make your answer incorrect.
So I think it would be better to say in a manner "it would not work that way but would work this way ..."
Monday, October 22, 2007 8:32 AM
If you have access to the db, my suggestion would be to create a stored procedure that orders the rows instead of a view. Your resultset will then be ordered.
Adam
Monday, October 22, 2007 1:09 PM
Hi again,
I think i have it at last thank to a composite of your suggestions! Using DTS I was able to copy the table and insert a sort query to get the table in the right order. Then I just copied the sorted table back into the original table and there it is, a sorted table.
I havent tested it properly yet but I will report back with the outcome in case this could be useful to others.
Cheers,
Richee.
Monday, October 22, 2007 1:18 PM
If you are going to do that then surely creating a script that copies the sorted query result into a new table would be an easier option?
Monday, October 22, 2007 1:52 PM
If it was a normal DB situation then yes you're right but the app server creates a rigid DB structure and will only look at the preconfigured tables it creates. No indexes, stored procedures etc just the tables it creates when the product is setup so thats why I have been so stuck.
Cheers,
Richee.
Monday, October 22, 2007 4:41 PM
| richee wrote: | |
|
Richee,
This is a very expensive approach. You also realize that as the records change, the DTS package will need to be run each time and every time.
A stored procedure will return a recordset and allow you to use an ORDER BY clause.
If you can query the view, you can query the stored procedure.
Wait. Are you using ADO in Java? If so, you can order the view in your SELECT statement (SELECT * FROM vw_MyView ORDER BY Name)
Adam
Monday, October 22, 2007 4:55 PM
| richee wrote: | |
|
In that case Richee would it be possible to have a sproc move the data to a temporary table and then sort the data before writing it back to the original table before running anything on the table?