#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2003
    Posts
    677
    Rep Power
    441

    Select top with order by


    I'm actually using Access, but hopefully this is similar (I know, lame, but it's the only thing our IT area lets us use for intranet development without going through hours of meetings and setup for an Oracle database).

    I've got a SQL statement like this:

    Code:
    SELECT top 5 * FROM Certificates ORDER BY CertDate DESC;
    This is meant to get me the last five certificates sent. Right now it's giving me all the certificates from the last 5 days (12/8-12/13).

    If I do this:

    Code:
    SELECT top 5 * FROM Certificates;
    I only get five entries, but they're not the latest five.

    I know just enough about this to be dangerous, what am I missing?
    Last edited by rdoyle720; December 14th, 2011 at 01:15 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by rdoyle720
    This is meant to get me the last five certificates sent. Right now it's giving me all the certificates from the last 5 days (12/8-12/13).
    all??? that doesn't sound right

    how many rows are returned for these last 5 days?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2003
    Posts
    677
    Rep Power
    441
    11 rows are returned, which is all the certificates from 12/8 to 12/13.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    that's astonishing

    presumably ms access is returning ties... can you post these 11 rows?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2003
    Posts
    677
    Rep Power
    441
    Here's the data that gets printed as an html table:

    Code:
    Date | From | To
    12/13/2011 | J Christensen | S Bartholf 
    12/13/2011 | D VandenHeuvel | D Reed 
    12/9/2011 | S Coles | S Curkeet 
    12/9/2011 | P Wegger | T Quarberg 
    12/8/2011 | B Harvey | D Heironimus 
    12/8/2011 | A Young | C Grahn 
    12/8/2011 | E Hixon | E Hixon 
    12/8/2011 | E Hixon | M Selchow 
    12/8/2011 | A Santolini | D Paull 
    12/8/2011 | A Fitch | S Martinson 
    12/8/2011 | D Mogensen | D Mogensen
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    your results, and a minute or so googling, confirm that microsoft access does indeed treat TOP as though it were TOP WITH TIES (an option used in microsoft sql server)

    so there you go

    i've always preferred the logic of WITH TIES anyhow

    i mean, whose certificate are you gonna reject?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2003
    Posts
    677
    Rep Power
    441
    Hmm, that's interesting, and thanks for your help!

    My hope was to do a "last 5 certificates sent" list...is there another way to do my query to get that?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by rdoyle720
    My hope was to do a "last 5 certificates sent" list...is there another way to do my query to get that?
    i'm still at a loss to understand why you would want to list these --

    12/13/2011 | J Christensen | S Bartholf
    12/13/2011 | D VandenHeuvel | D Reed
    12/9/2011 | S Coles | S Curkeet
    12/9/2011 | P Wegger | T Quarberg
    12/8/2011 | B Harvey | D Heironimus

    and yet leave these out --

    12/8/2011 | A Young | C Grahn
    12/8/2011 | E Hixon | E Hixon
    12/8/2011 | E Hixon | M Selchow
    12/8/2011 | A Santolini | D Paull
    12/8/2011 | A Fitch | S Martinson
    12/8/2011 | D Mogensen | D Mogensen


    i mean, how ~do~ you decide which certificates to leave out?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2003
    Posts
    677
    Rep Power
    441
    Because all I want is a quick peek at some of the latest certificates that have been sent. If 30 got sent today, and 50 got sent yesterday, I don't want to see 80, I just want to see 5.

    I think I see what you're getting at from a code standpoint, though. If the dates are the same, how does the system know which 12/8 item to return? With that in mind, maybe I should change the date entries to something more specific, like 12/8 at 9am.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by rdoyle720
    ... maybe I should change the date entries to something more specific, like 12/8 at 9am.
    now you're cookin'
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo