Post reply

Using top to display records

  • September 19, 2007 at 8:57 am

    #178478

    Hi all, I've a database with 4 fields, my problem is using TOP and getting the right display.

    the table is built like:

    Date                Field2      Field3      Field4

    ================================

    12/01/2007      one          two        three

    12/02/2007      four         five         six

    12/03/2007      seven      eight       nine

    if I use select top 2 * from

    order by [date] asc  in my stored procedure my results are

    12/01/207  one ....

    12/02/2007 four .....

    and not

    12/02/2007 four ..

    12/03/2007 seven ......

    I know I can change asc to desc in my SP but then when I display the data I get the last date displayed first and not last as in my first attempt. Is there a way round it?

    incidently the date format is in UK English i.e dd/mm/yyyy

  • September 19, 2007 at 9:16 am

    #734635

    12/02/2007 would never (without more) appear first when using TOP, regardless of the sort order.

  • September 19, 2007 at 9:21 am

    #734636

    thanks David, I've just worked out to do it. I use ROWCOUNT instead. This seem to work for me

    set rowcount 4

    select * from table order by field desc

    set rowcount 0

  • September 20, 2007 at 12:08 am

    #734828

    Hi,

    You can also do it like this:

    Select top 2 * from (select top 2 * from 

    order by date desc) TempTest

    order by date

    Remember, it has performance issues ...

    Thanks.

    AK

  • September 20, 2007 at 1:31 am

    #734843

    that's great, thanks

  • September 21, 2007 at 4:58 am

    #735173

    Anubhav,

    one of the TOP is not necessary. You can limit the output first, and then just order.

    SELECT *

    FROM (select top 2 * from

    order by date desc) as TempTest

    ORDER BY date

    And, of course, the * is there just because we don't know the real column names. It should not stay in the final version of code - using column names always is best practice.

  • September 21, 2007 at 5:49 am

    #735182

    many thanks for the solution

  • September 21, 2007 at 7:51 am

    #735241

    Thanks Vladan,

    Even I actaully realiazed it at the end of the day. It was actaully a quick solution and I just bunked off from my  work to answer it. Sheer enthusiasm u know !!

    Thanks.

    AK

  • September 27, 2007 at 12:13 am

    #736628

    Hi Mick,

    Long time, no see...

    Just an FYI... be real careful about using SET ROWCOUNT... if the queries after the SET have ORDER BY on large tables, you're going to have some really slow code. TOP works much better but, of course, is not programmable in SQL Server 2000.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 27, 2007 at 1:37 am

    #736639

    Thanks Jeff, yes, it has been a long time

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply