Select top 40 * from and Order By

Discussion in 'ASP General' started by Bryan Harrington, Dec 16, 2003.

  1. Can you use Select top 40 * from ... and ORDER BY... in the same query?

    I have the following 4 quearies that are.. for the most part, the same
    except the order by clause, all return a differnt amount of records.
    Thoughts?

    select top 40 * from q_work_list where assigned_to = 10 order by batch_date,
    remit_amt desc
    40 records returned.


    Sort by Reject Amt
    select top 40 * from q_work_list where assigned_to = 10 Order by remit_amt
    asc
    41 records returned.

    Sort By Batch Date
    select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
    desc
    110 records returned.


    Sort by Batch Again
    select top 40 * from q_work_list where assigned_to = 10 Order by batch_date
    asc
    143 records returned.

    And it doesn't make a difference if I use * or the appropriate field names..
    same results.

    TIA
    Bryan Harrington, Dec 16, 2003
    #1
    1. Advertising

  2. Bryan Harrington

    Bob Barrows Guest

    Bryan Harrington wrote:
    > Can you use Select top 40 * from ... and ORDER BY... in the same
    > query?
    >

    It depends on the database, and sometimes the version of the database <hint>

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows, Dec 16, 2003
    #2
    1. Advertising

  3. "Bryan Harrington" <> wrote in message
    news:OZjkDh$...
    > Can you use Select top 40 * from ... and ORDER BY... in the same

    query?
    >
    > I have the following 4 quearies that are.. for the most part, the same
    > except the order by clause, all return a differnt amount of records.
    > Thoughts?
    >
    > select top 40 * from q_work_list where assigned_to = 10 order by

    batch_date,
    > remit_amt desc
    > 40 records returned.
    >
    >
    > Sort by Reject Amt
    > select top 40 * from q_work_list where assigned_to = 10 Order by

    remit_amt
    > asc
    > 41 records returned.
    >
    > Sort By Batch Date
    > select top 40 * from q_work_list where assigned_to = 10 Order by

    batch_date
    > desc
    > 110 records returned.
    >
    >
    > Sort by Batch Again
    > select top 40 * from q_work_list where assigned_to = 10 Order by

    batch_date
    > asc
    > 143 records returned.
    >
    > And it doesn't make a difference if I use * or the appropriate field

    names..
    > same results.
    >
    > TIA


    In future posts, please provide database, version and appropriate DDL
    when asking a database related question. Also consider posting the
    message to the m.p.i.asp.database group instead.

    The behavior of the queries seems to indicate that you are using MS
    Access. In MS Access, the TOP predicate of the SELECT clause carries an
    implicit WITH TIES. So in your second example, both the 40th and the
    41st records have the same Reject Amt. Similarly in your 3rd example
    records 40-110 all have the same Batch Date.

    HTH
    -Chris Hohmann
    Chris Hohmann, Dec 16, 2003
    #3
  4. Bryan Harrington

    Jeff Cochran Guest

    On Tue, 16 Dec 2003 12:16:50 -0500, "Bryan Harrington"
    <> wrote:

    >Can you use Select top 40 * from ... and ORDER BY... in the same query?


    Yes. Or no. (Depends on database and version...)

    >I have the following 4 quearies that are.. for the most part, the same
    >except the order by clause, all return a differnt amount of records.
    >Thoughts?


    TOP 40 might return more than 40 on an ORDER BY if you have duplicates
    in whatever field you order by. In other words, if you ORDER BY Price
    and have 81 items at $1.00, *which* are the top 40?

    Jeff
    Jeff Cochran, Dec 16, 2003
    #4
  5. > TOP 40 might return more than 40 on an ORDER BY if you have duplicates
    > in whatever field you order by. In other words, if you ORDER BY Price
    > and have 81 items at $1.00, *which* are the top 40?


    In SQL Server at least, you can alter this behavior by the optional WITH
    TIES clause. I think Access just uses WITH TIES by default and you can't
    override it, but I'm not certain; likewise, I'm not sure if similar optional
    clauses are available in other RDBMSes.

    And FWIW, TOP without ORDER BY makes absolutely no sense in a relational
    database. You may as well say TOP 40 ARBITRARY...

    --
    Aaron Bertrand
    SQL Server MVP
    Aaron Bertrand - MVP, Dec 16, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jonathan Folland
    Replies:
    2
    Views:
    1,612
    Jonathan Folland
    Mar 17, 2005
  2. Frederic HOUDE
    Replies:
    4
    Views:
    586
    Frederic HOUDE
    Aug 31, 2004
  3. Cuthbert
    Replies:
    8
    Views:
    421
    Ancient_Hacker
    Sep 13, 2006
  4. willem joubert

    Error 403-Error 403-Error 403

    willem joubert, Feb 8, 2005, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    164
    Bruce Johnson [C# MVP]
    Feb 8, 2005
  5. palmiere
    Replies:
    1
    Views:
    384
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page