[SOLVED] Select Top 1* returns multiple results!

[wellscam]

Hey,

 

I'm querying an Access database using PHP with this query to return the last entry in the database and display how current it is.

 

$query = odbc_exec($odbc, "SELECT Top 1 * FROM AllInfo WHERE IO = 'Out' AND CO = '51' ORDER BY [DateTime] DESC") or die (odbc_errormsg()); 
while($row = odbc_fetch_array($query)) 
{ 
    echo 'Database current as of<strong> '.date("F j, Y, g:i a", strtotime($row['DateTime'])).'</strong> and will update every 23 minutes.'; 
} 

 

The problem is, if there are 2 or 3 identical records in there, it will return all of those records. How do I tell it to only return the first one? Or maybe add a second order by parameter?

[themistral]

Try

SELECT Top 1 * FROM AllInfo WHERE IO = 'Out' AND CO = '51' ORDER BY [DateTime] DESC LIMIT 0,1

 

Note the LIMIT at the end.

[wellscam]

$query = odbc_exec($odbc, "SELECT Top 1 * FROM AllInfo WHERE IO = 'Out' AND CO = '51' ORDER BY [DateTime] DESC LIMIT 0,1") or die (odbc_errormsg()); 

 

Returns:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause.

[Barand]

LIMIT is MySQL only.

 

It may be because your records do not have a unique primary key if the records are identical.

[wellscam]

Is there an Access version of the Limit function, or is there a way to add multiple Order By criteria so I can choose more than one column to sort by?

[Barand]

"TOP N" is the access (and MSSQL) version of MySQL's "LIMIT N".

 

 

to order ny multiple columns

 

... ORDER BY a, b, c

 

But there shouldn't be identical rows

 

 

[wellscam]

When I try this,

$query = odbc_exec($odbc, "SELECT Top 1 * FROM AllInfo WHERE IO = 'Out' AND CO = '51' ORDER BY [DateTime, Extension] DESC") or die (odbc_errormsg()); 

 

I get this error:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

 

I understand that there shouldn't be identical rows. Unfortunately, the database is being generated by a software program that I have no control over and it does not add a unique id for each row. It's a phone program that queries and logs our switch data into an Access database with simple data like Extension, Time, Date, Line Used, Incoming/Outgoing, Total Duration etc...

 

My result sometimes repeats a couple of times and I'm thinking that it's because there are multiple calls with the same timestamp, is there another reason my query would produce the result twice?

[Barand]

this looks wrong

ORDER BY [DateTime, Extension]

 

try

... ORDER BY [DateTime],[Extension] DESC

[wellscam]

Yeah, I guess that syntax helps!!

 

It's actually returning the first record in the database now though instead of the last!

 

May 1, 2008, 5:27 am

 

instead of

 

July 2, 2008, 3:25 pm

[DarkWater]

So do ASC instead of DESC.

[Barand]

or

 

... ORDER BY [DateTime] DESC, [Extension] DESC

[wellscam]

That seems to have done it Barand thank you so much for your patience and help on this!