Select top with order by

Dev Shed Forums Sponsor:

December 14th, 2011, 10:43 AM

rdoyle720

Contributing User

Join Date: Dec 2003

Posts: 646

Time spent in forums: 2 Weeks 3 Days 4 h 13 m 18 sec
Reputation Power: 400

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.

December 14th, 2011, 03:21 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

Quote:

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

December 14th, 2011, 03:34 PM

rdoyle720

Contributing User

Join Date: Dec 2003

Posts: 646

Time spent in forums: 2 Weeks 3 Days 4 h 13 m 18 sec
Reputation Power: 400

11 rows are returned, which is all the certificates from 12/8 to 12/13.

December 14th, 2011, 03:48 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

that's astonishing

presumably ms access is returning ties... can you post these 11 rows?

December 14th, 2011, 05:09 PM

rdoyle720

Contributing User

Join Date: Dec 2003

Posts: 646

Time spent in forums: 2 Weeks 3 Days 4 h 13 m 18 sec
Reputation Power: 400

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

December 14th, 2011, 05:25 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

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?

December 15th, 2011, 09:09 AM

rdoyle720

Contributing User

Join Date: Dec 2003

Posts: 646

Time spent in forums: 2 Weeks 3 Days 4 h 13 m 18 sec
Reputation Power: 400

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?

December 15th, 2011, 11:21 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

Quote:

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?

December 15th, 2011, 11:35 AM

rdoyle720

Contributing User

Join Date: Dec 2003

Posts: 646

Time spent in forums: 2 Weeks 3 Days 4 h 13 m 18 sec
Reputation Power: 400

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.