Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Unanswered: Top + Order By = Strange Results

    Hi there,

    I'm a little bit confused here.

    I the TOP 1 function with ORDER BY DESC to get the last id in my table but it doesn't seems to work.

    Here's an example:
    Code:
    SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESC
    
    
    SELECT ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASC
    So basically the first SELECT should return the last id value of the second query but it doesn't.

    The first query gives me that: 60721

    And the second one gives me that:
    60680
    60681
    60683
    60684
    60685
    60686
    60718
    60719
    60720
    60721
    61050
    61122
    61124

    So as you can see my TOP 1 ID ORDER BY ID DESC should gave me this result: 61124


    Am I missing something or... ?


    Please help me this is aleready in function so I have to fix it ASAP.

    Thank you,
    Regards,

    OR-THO
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Warning: This will exclusively lock the Worksheet table while it runs. You probably don't want to run this in a production database.
    Code:
    BEGIN TRANSACTION
    SELECT Count(*) FROM Worksheet (TABLOCKX) WHERE 'xyzzy' = Style
    SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESC
    SELECT       ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASC
    SELECT Max(ID)  FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System'
    ROLLBACK TRANSACTION
    This will get the "moving pieces" out of the way in case there are changes being caused by other DML executing while you weren't looking.

    -PatP

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I don't fallow you on this one.

    First how can we use MAX without GROUP BY ?

    And second, why is my query returning this strange result?

    I just don't understand.

    SELECT TOP 1 fld FROM tbl ORDER BY fld DESC should returns the maximum value of this fld... no ?

    I am completely lost and confused...
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ortho
    I am completely lost and confused...
    did you run the stuff pat posted? what were the results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    It doesn't seems to work:

    result1 : 0
    result2 : 60721
    result3 : 60680
    60681
    60683
    60684
    60685
    60686
    60718
    60719
    60720
    60721
    result4 : 60721
    Last edited by ortho; 03-17-08 at 10:09.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Shit guys I'm sorry about that.
    That was only a typo in the title.

    FORM instead of FROM...

    Once again I'm really sorry for this.

    I'll buy you all a beer
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

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