How can I reorder rows in sql database

Question

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.

---

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 1

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:

if (increment) {
  update MyTable
    Set ListOrder=ListOrder-1
        where ListOrder<=1 -- The New position
            and ListOrder >(Select ListOrder from MyTable where BookMark='f')
} else {
  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 2

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 3

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 4

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.