select top with order by

[thehappyappy]

I wonder if somebody can help me please. I'm trying to get only ten results from a database, but it won't do that when I add an order by.

This is what I've got so far:sql="SELECT TOP 10 * FROM listings WHERE active <> 0 order by idate, idate" This gets 20 results, if I remove the idate at the end it then gets all of the results. If I remove the order by completely then I get 10, but they're not ordered by the date.

I'm trying to get the most recent results.

I've never used ASP, I normally us PHP and can just use LIMIT with that.

I'd be very grateful for any help with this.

Thanks in advance

[Old Pedant]

Nothing to do with ASP *OR* PHP. Has to do with the database you are using.

MySQL uses LIMIT 10.

SQL Server and Access use SELECT TOP 10.

You don't say which DB you are using, and the answer is (slightly) different depending on the DB.

However...

The problem with SELECT TOP 10 is that it *WILL* return TIES. That is, if you have more than one record with the same idate value, it returns the TOP 10 idates, including all ties.

There's an easy fix that works with either Access or SQL Server: Tack on *another* field to the ORDER BY. You get to decide what field to use, but if you don't care a lot, then use the PRIMARY KEY field of the table.

So
... ORDER BY idate, whateverThePrimaryKeyFieldIsNamed

[Dee99]

you have idate twice at the end of your sql , not sure why

" order by idate, idate"

[Old Pedant]

I think that was a typo. I think its actually "ORDER BY someotherfield, idate"

But not that it matters: The problem is clearly that the SELECT TOP 10 is getting ties.

I'm pretty sure that the DB in use here is Access. Because SQL Server, by default, doesn't get ties. You have to tell it to:
http://msdn.microsoft.com/en-us/library/ms189463.aspx

But I'm 90% sure that the default with Access is always "WITH TIES" and you have to specify an additional field in the ORDER BY to break the ties.

[thehappyappy]

I think that was a typo. I think its actually "ORDER BY someotherfield, idate"

But not that it matters: The problem is clearly that the SELECT TOP 10 is getting ties.

I'm pretty sure that the DB in use here is Access. Because SQL Server, by default, doesn't get ties. You have to tell it to:
http://msdn.microsoft.com/en-us/library/ms189463.aspx

But I'm 90% sure that the default with Access is always "WITH TIES" and you have to specify an additional field in the ORDER BY to break the ties.

You're right it is an access database, but the problem is I want to order by date - hence the ORDER BY idate, idate. I tried ordering by id or title, but that didn't work either. Is it possible to kind of trick it by instead of having an order by have SELECT TOP 10 FROM listings WHERE active <> 0 AND idate < today's date?

[Old Pedant]

I don't think you read my post, at all.

As I said, just add *ANOTHER* field to the ORDER BY, to break the ties.

...ORDER BY idate, xxx

Where, best case, xxx is the primary key of the table.

And if you mean you want the MOST RECENT records first, then that is no different in Access than in any other DB:

... ORDER BY idate DESC, xxx

[Dee99]

I think that was a typo.

not a typo....

regardless, I did test the original query in Access and it seems to work regardless of having one or two "idate" at the end.

Even with ties, it still only returned ten results for me.

Test the query yourself directly in Access. If it works for you too then the problem must be somewhere else.

[Old Pedant]

Not according to the Access documentation--and not according to my own experience.

For example:
http://office.microsoft.com/en-us/access-help/all-distinct-distinctrow-top-predicates-HP001032205.aspx?CTT=5&origin=HP001032265


SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.


So, Dee, I don't know how you tested it, but I think your test was flawed.

[Old Pedant]

To clarify, using Microsoft's own example, if you changed that query in MS docs to (say)
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC, LastName ASC

*THEN* you will only get 25 records.

*UNLESS* it just so happens that there are two people with the 25th best GradePointAverage who have the same LastName!

Okay, so then you would do
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC, LastName ASC, FirstName ASC

And now you are okay unless ... well, you get the idea. If you have a primary key in the table (say STUDENTID, which is an AUTONUMBER field), then it's best to use it:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC, StudentId ASC

And then you would never have "equal" records and would ensure you get exactly 25 records.

[Dee99]

I don't know how you tested it, but I think your test was flawed.


pretty simple test --

-create a table called listings
-add 3 fields called id/active/idate as type: primarykey / integer / datetime
-enter 12 records of data - make a couple as equal dates
-run the query

try it yourself...but it worked for me

[Old Pedant]

Okay...

<%
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("/access_db/test.mdb")

SQL = "CREATE TABLE foo ( thedate DATETIME, name VARCHAR(50) )"
conn.Execute SQL

For n = 1 TO 12
SQL = "INSERT INTO foo VALUES( DATE(), 'name " & n & "')"
conn.Execute SQL
Next

Set RS = conn.Execute("SELECT TOP 3 * FROM foo ORDER BY thedate")
Do Until RS.EOF
Response.Write RS(0) & ": " & RS(1) & "<br/>" & vbNewLine
RS.MoveNext
Loop
RS.Close
Conn.Close
%>

The results:

11/19/2010: name 12
11/19/2010: name 11
11/19/2010: name 10
11/19/2010: name 9
11/19/2010: name 8
11/19/2010: name 7
11/19/2010: name 6
11/19/2010: name 5
11/19/2010: name 4
11/19/2010: name 3
11/19/2010: name 2
11/19/2010: name 1

[Old Pedant]

And, of course, if I change the last query to
Set RS = conn.Execute("SELECT TOP 3 * FROM foo ORDER BY thedate, name")

Then the results are

11/19/2010: name 1
11/19/2010: name 10
11/19/2010: name 11

Correctly.

[Dee99]

the test you ran seems a bit "flawed".

if the database dates are all the same then the 'order by ' clause would not be needed.

if any of the dates are different then the query results are also different

this inconsistent result appears to be a bug ( in Access ?).

[Old Pedant]

I will try one more time.

<%
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("/access_db/lawyers.mdb")

conn.Execute "DROP TABLE bar"

SQL = "CREATE TABLE bar ( thedate DATETIME, name VARCHAR(50) )"
conn.Execute SQL

For n = 1 TO 15
theDate = DATE() + INT( n / 5 )
SQL = "INSERT INTO bar VALUES( #" & theDate & "#, 'name " & n & "')"
Response.Write SQL & "<br/>"
conn.Execute SQL
Next
Response.Write "<hr>"
Set RS = conn.Execute("SELECT TOP 7 * FROM bar ORDER BY thedate")
Do Until RS.EOF
Response.Write RS(0) & ": " & RS(1) & "<br/>" & vbNewLine
RS.MoveNext
Loop
RS.Close
Conn.Close
%>

The results:

INSERT INTO bar VALUES( #11/19/2010#, 'name 1')
INSERT INTO bar VALUES( #11/19/2010#, 'name 2')
INSERT INTO bar VALUES( #11/19/2010#, 'name 3')
INSERT INTO bar VALUES( #11/19/2010#, 'name 4')
INSERT INTO bar VALUES( #11/20/2010#, 'name 5')
INSERT INTO bar VALUES( #11/20/2010#, 'name 6')
INSERT INTO bar VALUES( #11/20/2010#, 'name 7')
INSERT INTO bar VALUES( #11/20/2010#, 'name 8')
INSERT INTO bar VALUES( #11/20/2010#, 'name 9')
INSERT INTO bar VALUES( #11/21/2010#, 'name 10')
INSERT INTO bar VALUES( #11/21/2010#, 'name 11')
INSERT INTO bar VALUES( #11/21/2010#, 'name 12')
INSERT INTO bar VALUES( #11/21/2010#, 'name 13')
INSERT INTO bar VALUES( #11/21/2010#, 'name 14')
INSERT INTO bar VALUES( #11/22/2010#, 'name 15')
--------------------------------------------------------------------------------
11/19/2010: name 4
11/19/2010: name 3
11/19/2010: name 2
11/19/2010: name 1
11/20/2010: name 9
11/20/2010: name 8
11/20/2010: name 7
11/20/2010: name 6
11/20/2010: name 5

As you can clearly see, NOW not all of the date values are the same. (Not that I see how that makes ONE WHIT of difference. But I changed it to satisfy you.)

And I am clearly asking for "TOP 7".

And just as clearly it is returning 9 records. Because, indeed, there are TIES at position 7 and Access WILL NOT break the ties by itself.

I have no idea what YOUR test looked like.

Well, maybe I have a hunch. You wrote
-enter 12 records of data - make a couple as equal dates
THAT IS NOT ENOUGH.

ONLY if the TIES occur at the LAST POSITION selected (that is, at position 7 when you do TOP 7; at position 10 when you do TOP 10) will the ties MATTER.

[Old Pedant]

this inconsistent result appears to be a bug ( in Access ?).

WHAT "inconsistent result"?????

I find no inconsistencies whatsoever. The queries are clearly doing what the documentation says.

The fact that Access works differently than MySQL is not an inconsistency; it's simply a difference.