How can I reorder rows in sql database

by

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'

More Related Contents:

  1. how to save sql query result to csv in pandas
  2. How to delete the records by using SQL [closed]
  3. How do I obtain a Query Execution Plan in SQL Server?
  4. MySQL: Alternatives to ORDER BY RAND()
  5. IN vs ANY operator in PostgreSQL
  6. sql query to return differences between two tables
  7. Generate SQL Create Scripts for existing tables with Query
  8. SQL Server 2005 How Create a Unique Constraint?
  9. Is there any boolean type in Oracle databases?
  10. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  11. Dynamic Oracle Pivot_In_Clause
  12. sql server select first row from a group
  13. How to make SQL many-to-many same-type relationship table
  14. Convert Unixtime to Datetime SQL (Oracle)
  15. SQL Use alias in Where statement
  16. When to Denormalize a Database Design
  17. How to find a table having a specific column in postgresql
  18. PostgreSQL JOIN with array type with array elements order, how to implement?
  19. Trim trailing spaces with PostgreSQL
  20. Nth max salary in Oracle
  21. divide by zero/null workaround in SSRS 2008 report
  22. What does sp_reset_connection do?
  23. Replace first occurrence of substring in a string in SQL
  24. How to correctly insert newline in nvarchar [duplicate]
  25. Check whether string is a date Postgresql
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL Server equivalent of WM_CONCAT function [duplicate]
  29. Determine what user created objects in SQL Server
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment