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:

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'