Hi,

I don't know how to do what I need to, so I will just describe what I require and maybe someone else will explain how I do it.

I'd like a field in my database to identify records. I would like the field to contain the row number.

For example, this field would equal 1 for my first record, 2 for my second and 3 for my third etc.

I did try auto_increment but it appears that if I have 3 records and I remove the first, the numbers don't adjust accordingly, leaving me with 2 and 3 respectively.

I hope this makes sense and thank you for any time you spend on my query.

Carl
(carl@wolsey.info)

    I forgot to mention that it is in fact PHP/MySQL that I'm working with incase it isn't obvious to anyone.

      The reason for this is that it is a unique identifier...

      That means that logically, you cant have two records with the same id-number.

        There wouldn't be two of the same record though.

        This is what my database looks like:

        ID DATAFIELD

        1 TEST
        2 BLAH
        3 BLAHHHH

        This is what currently happens when I remove the first record:

        ID DATAFIELD

        2 BLAH
        3 BLAHHHH

        And this is what I would like to happen:

        ID DATAFIELD

        1 BLAH
        2 BLAHHHH

        See... the ID field always equals the row that it's record is on.

        Thanks

          I think you can database triggers for this.
          It will fire on each delete on specified table and recalculate that number

          Dan

            • [deleted]

            • Post #6

            Theoretically yes, but practically no.

            The point is that records in a database don't have rownumbers, they just don't, and you should not try to give them rownumbers.

            and why not? Because records in a database have no order whatsoever. The database inserts records wherever it sees fit.

            The only order that exists is the one you force using the ORDER BY clause in your query.

            If you want to show a rownumber (if there is such a thing) you should just add a counter in your script and print that.

            A forum, a FAQ, what else do you need?

            Time to YAPF!: http://www.hvt-automation.nl/yapf/

              That's exactly what I decided to do, and have done.

              Thanks for your help guys.

                Write a Reply...