SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    Buffalo, NY
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Sort by Transaction

    Currently our program creates a random integer for an Identifier, now our problem is how to sort that so there is the last transaction at the bottom of the file, because we can delete any data we wish, the file (to prevent fragmentation?) uses the first availible block reserverd for the database

    ie:
    ID | Data
    ------------
    300 | apple
    231 | pear
    87 | grape

    Delete 231 | pear

    ID | Data
    ------------
    300 | apple
    87 | grape

    Insert 438 | rudeabegga

    ID | Data
    ------------
    300 | apple
    438 | rudeabegga
    87 | grape

    How would I sort it so my result set would look like:

    ID | Data
    ------------
    300 | apple
    87 | grape
    438 | rudeabegga

    ie: based on insertion number (and without adding an insertion number field)?

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What DBMS? And why are you creating a random number?

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have 2 choices, don't insert a random number (increment it)...


    Or what I would do...
    add a "InsertDate", "ChangeDate", "AddedDate", or whatever you wanna call it to the table. When you insert the data, also insert getDate() for the field pre-mentioned. Sort on it, and you get the latest/most recent record inserted. I just prefer my way, don't like to trust ID's fields in the way you're wanting to use 'em...just me though.


    EDIT: well, since you say you don't want to add another field, I echo matt's ?, and the top portion of my reply

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    Buffalo, NY
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) The reason I'm using a random number is that I can easily have over 4.something Billion transactions per table (which is the limit of the id filed int(4)?)

    2) I reallsy didn't want to have to modify a couple hundred tables with a date field (but it seems like the most logical choice)

    This database is (among other things) tracking widgets in producution, and that is why I can have 4 billion in the course of a year or 2. The problem with incrimental is that after a year, I would delete the last years complete and shiped widgets, unfortunatley the Max + 1 idea (to get the next larget number will then never re-use 1 - 1000000 even though they are able to be reused (which a random number will allow)

    I guess I'm going to have to add a field (unless someone knows how to sort by the interal transaction number of SQL Server.

    (Some clients use SQL-7 and others SQL2000)

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    unfortunatley the Max + 1 idea (to get the next larget number will then never re-use 1 - 1000000 even though they are able to be reused (which a random number will allow)
    Since you are using SQL Server if you use identity columns yes you can re-seed it to start at 1 again.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hmmm

    you say you can easily have 4 billion rows in a year

    that would mean you are inserting at the rate of 46,296 per second

    no offence, but i personally have never seen an application like that, so i shall give you the benefir of the doubt and just accept that this is possible

    my answer would still be: use BIGINT

    with BIGINT, you can store numbers up to 9,223,372,036,854,775,807

    that's 9 quintillion, and just to give you an idea of how big that is, you can add rows at your current rate, 46 thousand per second, and not run out of numbers for five thousand years

    personally i think using BIGINT IDENTITY is as simple as it gets, there's no re-seeding required, nor any special function to generate numbers, no fuss, no muss...

    r937 say: from several solutions, choose the simplest



    p.s. sorry, i do not know how many releases back you can say BIGINT IDENTITY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    hmmm

    you say you can easily have 4 billion rows in a year

    that would mean you are inserting at the rate of 46,296 per second

    dadgum Rudy, you kill me w/some of the things you say...that just came across as pretty funny.

    I'm gonna use that as sig material.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    heh, thanks

    and i have to apologize, my math was wrong

    due to the large size of these numbers, i was doing it with paper and pencil and not my calculator as usual

    anyhow, i double-checked, and 46,296 per second would exceed the BIGINT limit only after 6 million years, not 5 thousand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    May 2004
    Location
    Buffalo, NY
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We are now using BigInt, and that should do it, the reason I can have 4 Billion in a year or 2 (prolly more like 3-5 years) is that the code that was writen was really bad about handleing things. I can't use an identity, (told by my boss) so... BigInt it is and thanks!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your boss should not stick her nose into technical matters that she doesn't understand, and you can tell her i said that

    unless of course there's a CLUSTERED index and the idea of the random identifier is to spread inserts all over the place

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    Buffalo, NY
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And happily BigInt does not exist on SQL 7 (WHEEEEEEE) so now I guess I'm going to a numberic(28,0)

    Thanks for all your help.

    Do you think 9,999,999,999,999,999,999,999,999,999 is enough? ^_^ jk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •