CODEWITHSUNDEEP
phpmysqlsql
Adam
Adam

Reputation: 493

SQL Select latest row where value matches

I'm trying to return the row from my database, with the highest UID, where the URL column matches http://urltocheck.com.

I've tried all manner of things I can think of, and this is the closest I can get, but I'm getting an SQL syntax error.

My Table is called Adam, and I have the columns... UID (unique), URL (plus loads more). I'm trying to access the MySQL databse via PHP. 

$query = "SELECT * FROM `Adam`
    WHERE URL='http://urltocheck.com'
    ORDER BY `UID` ASC;
    LIMIT 1;";

Can anyone help please?

Upvotes: 0

Views: 67

Answers (4)

Mubo
Mubo

Reputation: 1070

You can also ORDER BY MAX ID. 

<?php 

$query = "SELECT * FROM `Adam`
    WHERE URL='http://urltocheck.com'
    ORDER BY MAX(`UID`) DESC;";

This is executed faster. 

$query = "SELECT * FROM `Adam`
        WHERE URL='http://urltocheck.com'
        ORDER BY MAX(`UID`);";

?>

Upvotes: 0

Rahul
Rahul

Reputation: 77934

Try like this. Also, remove ; at this line ORDER BY UID ASC; (didn't noticed that earlier) because of which limit 1 not coming to picture.

SELECT * FROM `Adam`
    WHERE URL='http://urltocheck.com'
    and `UID` = (select max(`uid`) from `Adam`)

Upvotes: 1

Pedro Lorentz
Pedro Lorentz

Reputation: 2326

with the highest UID

You should order by UID desc and limit to 1.

Upvotes: 0

Pablo Matias Gomez
Pablo Matias Gomez

Reputation: 6823

You shoul use order DESC and remove the ";" after ASC

$query = "SELECT * FROM `Adam`
WHERE URL='http://urltocheck.com'
ORDER BY `UID` DESC
LIMIT 1";

Upvotes: 2

Related Questions