Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: Default sort order when order by column value are all the same

    Hi,
    We got a problem.
    supposing we have a table like this:

    CREATE TABLE a (
    aId int IDENTITY(1,1) NOT NULL,
    aName string2 NOT NULL
    )
    go
    ALTER TABLE a ADD
    CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
    go


    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    ...
    ... (20 times)

    select top 5 * from a order by aName
    Result is:
    6 Bank of abcde
    5 Bank of abcde
    4 Bank of abcde
    3 Bank of abcde
    2 Bank of abcde

    select top 10 * from a order by aName
    Result is:
    11 Bank of abcde
    10 Bank of abcde
    9 Bank of abcde
    8 Bank of abcde
    7 Bank of abcde
    6 Bank of abcde
    5 Bank of abcde
    4 Bank of abcde
    3 Bank of abcde
    2 Bank of abcde

    According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.

    Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

    So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by silentcat

    Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
    Finding all nof your code like this should not be a big problem if you keep all of your sql in stored procedurees, views, triggers, functions etc... and none of it is in line in your application code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem can be traced back to the person who designed your table with an IDENTITY column

    every time you use an IDENTITY column, you should also find an alternate key which can be declared unique

    this is basic database design, something every database designer should know

    in the example you cited, it would be aName

    that way, you would not even be able to insert those dupes in the first place


    good luck tracking down all your recalcitrant sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Mmmmm...rudy....I get the impression here that the OP does not have an issue with duplicate names values. Perhaps there are additional columns that differentiate the records (a composite natural key). I'm suspecting we do not have all the information we need, but that this is just a general example.
    So my answer to the OP would be the classic, "Data in a database has no inherent order". If he wants to ensure that data is returned in a specific order, he will need to specify it in each query statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is no guarantee that this will work because it relies on an undocumented behavior, but at least a small empirical test does work:
    Code:
    CREATE TABLE a (
       aId		int		IDENTITY(1,1) NOT NULL
    ,  aName	NVARCHAR(25)	NOT NULL
    )
    go
    
    ALTER TABLE a
       ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
    go
    
    
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    insert into a values ('bank of abcde');
    
    select top 5 * from a order by aName
    
    select top 10 * from a order by aName
    
    ALTER TABLE a
       DROP CONSTRAINT PK_a
    
    ALTER TABLE a
       ADD CONSTRAINT PK_a
       PRIMARY KEY CLUSTERED (aName, aId)
    
    ALTER TABLE a
       ADD CONSTRAINT XIF01_a
       UNIQUE (aId)
    
    select top 5 * from a order by aName
    
    select top 10 * from a order by aName
    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another "peekaboo" answer

    pat, would it kill you to also copy/paste the actual results of those queries?

    some of us may be reading your answer, be really interested in it, and yet not be in a position to fire up the software necessary to run the script

    we are left totally in the dark as to what your script produces

    thus, we can only conclude that while your answers might be illuminating, you are simply playing peekaboo with us, toying with us, teasing us, and therefore we tend to discount your answers as inconsequential

    with the result that we tend to pay less and less attention to your posts, whether or not they contain scripts, because of your cavalier attitude towards us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All you'd need to do is ask nicely.

    The folks at DBForums tend to expect some degree of effort from those asking for help. In general, I expect some degree of effort from those trying to learn from the answers too. That's why I provide script, so that people can load it, run it, and if need be they can change it. Canned output is irrelevant to me, but since you asked for it:
    Code:
    aId         aName                     
    ----------- ------------------------- 
    6           bank of abcde
    5           bank of abcde
    4           bank of abcde
    3           bank of abcde
    2           bank of abcde
    
    aId         aName                     
    ----------- ------------------------- 
    10          bank of abcde
    9           bank of abcde
    8           bank of abcde
    7           bank of abcde
    6           bank of abcde
    5           bank of abcde
    4           bank of abcde
    3           bank of abcde
    2           bank of abcde
    1           bank of abcde
    
    aId         aName                     
    ----------- ------------------------- 
    1           bank of abcde
    2           bank of abcde
    3           bank of abcde
    4           bank of abcde
    5           bank of abcde
    
    aId         aName                     
    ----------- ------------------------- 
    1           bank of abcde
    2           bank of abcde
    3           bank of abcde
    4           bank of abcde
    5           bank of abcde
    6           bank of abcde
    7           bank of abcde
    8           bank of abcde
    9           bank of abcde
    10          bank of abcde
    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    All you'd need to do is ask nicely.
    That seems to be where I always go wrong. Sensing a pattern here...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by silentcat
    Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

    So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
    The short answer is no. In case you missed it earlier, the physical order of data in a database has no meaning (there is no "default order"). Sets are by definition unordered. Therefore you use an Order By clause.

    I am afraid the best I can suggest is to get familiar with the workings of a text editor\ Visual Studio\ T-SQL for finding the offending order by statements and then changing manually or, if you feel adventurous, Find & Replace. Obviously this time you want to make sure everything is QAed properly before going into prod to avoid another problem.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pootle makes a good point... In theory, there is no order within a database. You can inflict order on a result set as it leaves the database using the ORDER BY clause, but within the database engine any order is at the whim of the engine itself.

    When you specified that you wanted the statements ordered by aName, that is exactly what the engine did. It put the results in order by aName and returned them to the client. The only way that you noticed a "problem" is because there was another column that you decided mattered to you "after the fact" when it was delivered to the client... The database engine had done just exactly what you requested.

    The kludge that I provided relies on knowledge of how the database engine does things, and it is by no means guaranteed. I happen to know a bit about how the engine does things, and found a parlor trick to coerce it into doing what you want. It might be enough to get you through until you can fix the problem, but it is purely a kludge.

    -PatP

  11. #11
    Join Date
    Sep 2005
    Posts
    2
    I must say, I begin to love this place.

    I get your ideas. Thanks for all the warm replies above.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by silentcat
    I must say, I begin to love this place.
    Cool. We could use a cat around here. We have a flump, but it was never neutered so it occasionally starts marking its territory and stinking up the place.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I didn't this place would be overrun with flumps. Do you not remember what happened when they neutered their flump down at sql-ninja-coding.com? It was carnage!!!!1111!!!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, if your pee keeps SQLUSA away, then by all means, let it fly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quick question Pat - does your undocumented behaviour always work? What about a merry-go-round scan?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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