How can I reorder rows in sql database

Question

Hi,

Is it possible to reorder rows in SQL database? For example; how can I swap the order of 2nd row and 3rd row's values?

The order of the row is important to me since i need to display the value according to the order.

Answer 1

Use ORDER BY in your SELECT query. For example, to order by a user's last name, use:

SELECT * FROM User ORDER BY LastName

Answer 2

The order of the rows on the actual database should not matter.

You should use the ORDER BY clause in your queries to order them as you need.

Answer 3

I guess a simple order by would be what you're looking for?

select my_column from my_table order by my_order_column;

Answer 4

As others have stated use an order by.
Never depend on the order data exists in a physical table, always base it of the data you are working with, be it one or more key fields.

Answer 5

Thanks for all the answers. But 'Order by' won't work for me.

For example, I put a list of bookmarks in database. I want to display based on the result I get from query. (not in alphabet order). Just when they are inserted.

But user may re-arrange the position of the bookmark (in any way he/she wants). So I can't use 'order by'.

An example is how the bookmark display in the bookmark in firefox. User can switch position easily. How can I mention that in DB?

Thank you.

Answer 6

Databases can store the data in any way they want. Using the "order by" clause is the only way to guarantee an ordering of the data. In your bookmark example, you could have an integer field that indicates the ordering, and then update that field as a user moves things around. Then ORDER BY that column to get things in the right order.

Answer 7

As others have mentioned, it's not a good idea to depend on the physical order of the database table. Relational tables are conceptually more like unordered sets than ordered lists. Assuming a certain physical order may lead to unpredictable results.

Sounds like what you need is a separate column that stores the user's preferred sort order. But you'll still need to do something in your query to display the results in that order.

It is possible to specify the physical order of records in a database by creating a clustered index, but that is not something you'd want to do on an arbitrary user-specified basis. And it may still lead to unexpected results.

Answer 8

In response to your post here, the answer you may be looking for is:

To order chronologically, add a DateAdded or similar column with a datetime or smalldatetime datatype.

On all methods that insert into the database, make sure you insert CURRENT_TIMESTAMP in the DateAdded column.

On methods that query the database, add ORDER BY DateAdded at the end of the query string.

NEVER rely on the physical position in the database system. It may work MOST of the time but definitely not ALL of the time.

Answer 9

It sounds like you need another column like "ListOrder". So your table might look like:

BookMark ListOrder
======== =========
    d                1
    g                2
    b                3
    f                4
    a                5

Then you can "order by" ListOrder.

Select * from MyTable Order By ListOrder

If the user can only move a bookmark one place at a time, you can use integers as the ListOrder, and swap them. For example, if the user wants to move "f" up one row:

Update MyTable
    Set ListOrder=ListOrder+1
        Where ListOrder=(Select ListOrder-1 From MyTable where BookMark='f')

Update MyTable
    Set ListOrder=ListOrder-1
        Where BookMark='f'

If the user can move a bookmark up or down many rows at once, then you need to reorder a segment. For example, if the user wants to move "f" to the top of the list, you need to:

update MyTable
    Set ListOrder=ListOrder+1
        where ListOrder>=1 -- The New position
            and ListOrder <(Select ListOrder from MyTable where BookMark='f')

 update MyTable
     Set ListOrder=1 -- The New Position
         Where Bookmark='f'

Answer 10

First, let me agree with everyone here that the order in the table shouldn't matter. Use a separate [SortOrder] column that you update and include an Order By clause.

That said, SQL Server databases do allow for a single "clustered index" on a table that will actually force the position in the underlying table storage. Primarily useful if you have a big dataset and always query by something specific.

Answer 11

The question lacks any detail that would let anyone give you correct answer. Clearly you could read the records into memory and then update them. But this is bad on so many different levels.

The issue is like this. Depending on the schema that is actually implemented there is logic to the way that the records are physically written to disk. Sometimes they are written in order of insert and other times they are inserted with space between blocks (see extents).

So changing the physical order is not likely without swapping column data; and this has a deep effect on the various indices. You are left having to change the logical order.

As I read your update... I'm left to understand that you may have multiple users and each user is to have bookmarks that they want ordered. Looks like you need a second table that acts as an intersection between the user and the bookmark. Then all you need is an inner join and an order by.

But there is not enough information to offer a complete solution.

Answer 12

Add a position column to your table and store as a simple integer.

If you need to support multiple users or lists, your best bet is to create a bookmarks table, an users table and a table to link them.

• bookmarks: id,url
• users: id,name
• users_bookmarks: user_id, bookmark_id, position, date_created

Assuming date_created is populated when inserting rows you can get secondary list ordering based on date.

select bookmark_id from users_bookmarks where user_id = 1 order by position, date_created;