6 Replies Latest reply on Feb 6, 2008 2:56 PM by Newsgroup_User

    an easier way? SQL related

    Level 7
      My director app reads in a particular column of every row in a DB table,
      and them presents the data in a text member. It looks like this

      ItemA
      ItemB
      ItemC
      etc...

      I want users to be able to 'reorder' the list however they want, and
      have that order preserved for the next time they run the app. The table
      has an ID column as Primary Key. My idea of how to do this is to issue a
      series of SQL UPDATE commands to change the ID of each row
      appropriately, so that subsequent reads will display the data list in
      the new order.

      I know how to do this, but wanted to check if I am not overlooking a
      better way.

        • 1. Re: an easier way? SQL related
          Level 7
          Depending on whether your DB supports stored procedures, you could add
          (or modify) one that implements an "order by" clause so that it's stored
          in the DB
          • 2. Re: an easier way? SQL related
            Chunick Level 3
            Do not change the id of each record... of course I'm not completely clear what you mean by that, exactly, but it doesn't sound like a good idea... when you query the data you can order it by ascending or descending order thru your sql query, like so:

            SELECT * FROM tblAccounts ORDER BY tblFirstName DESC

            ASC = ascending, A-Z, 0-9, etc. *
            DESC = descending, Z-A, 9-0, etc.
            *Note: that ASC is implied when ordering, so it does not have to be added, but it may be easier if trying to dynamically code it.
            • 3. Re: an easier way? SQL related
              Level 7
              Sean Wilson wrote:
              > Depending on whether your DB supports stored procedures, you could add
              > (or modify) one that implements an "order by" clause so that it's stored
              > in the DB

              SQLite does not support stored procedures. Thus the 'Lite' ;-)
              • 4. Re: an easier way? SQL related
                Level 7
                Chunick wrote:
                > Do not change the id of each record... of course I'm not completely clear what
                > you mean by that, exactly, but it doesn't sound like a good idea... when you
                > query the data you can order it by ascending or descending order thru your sql
                > query, like so: SELECT * FROM tblAccounts ORDER BY tblFirstName DESC ASC =
                > ascending, A-Z, 0-9, etc. * DESC = descending, Z-A, 9-0, etc. *Note: that ASC
                > is implied when ordering, so it does not have to be added, but it may be easier
                > if trying to dynamically code it.
                >

                I want the user to be able to determine the order, which won't
                necessarily be ascending or descending. The id is a column that is
                identified as the Primary Key, which means the id gets automatically
                assigned by the DB each time a new record is inserted. The DB allows you
                to change the Primary Key as long as it is unique.

                Basically it goes like this. The user can insert a number of rows into
                the table. The interface I am building displays data from the rows in
                the order they were entered. Later the user decides they want to change
                the order in which they are displayed.

                So they way I have this working now is by swapping the id of the rows
                around. A simple scenario would be swapping rows 2 and 3. The pseudo
                goes something like this

                Select row 3 and save it in a variable.
                Delete row 3
                Change row 2's id to 3
                Insert a new row, using the data saved in the variable
                Change the new row's id to 2

                This seems to work just fine, but I get the feeling there is an easier way.
                • 5. Re: an easier way? SQL related
                  Chunick Level 3
                  do not change your primary key. Bad database design. Instead, create another field... call it fldOrder, for instance... and use that to change the order of records based on the user's input. Changing the record's ID may not affect anything in the situation you describe, but if you ever need to create another table and are referencing that table in the newly created one by the primary key then your current system will not work... again, it's bad database design and should be avoided. Create the extra field to keep track of the order.... set the data type as an int or small int or tiny int... whatever the database supports.
                  • 6. Re: an easier way? SQL related
                    Level 7
                    "do not change your primary key. Bad database design."

                    See I knew there was a reason to ask! lol. I get what your saying about
                    creating another field to keep track of the order. I'll go with that.
                    Thanks Chunick