MySQL Subselect problem

[larry98765]

Hi All,

I have a table called "articles." Each article has a date field called "pubdate" -- the article's date of publication. Many articles share the same publication date.

I need to perform the following sql query:

"Show me ONLY the articles with the most recent pubdate."

So the query should be something like this:

SELECT *
FROM articles
WHERE pubdate =
'SELECT pubdate
FROM articles
ORDER BY pubdate DESC
LIMIT 1'

This SHOULD select all the pubdates, order them and limit to the first (that's the most recent article.) Then the outer select should find me all the articles that are also published on that date.

I know the data is there, but this query yields no results. I think there's a syntax problem, but I'm not sure where.

Any ideas?

Thanks

 

[ScottW]

Mysql doesn't support sub-selects.

SELECT * FROM articles ORDER BY pubdate DESC LIMIT 10;

This will pull the most recent 10 last published articles.

You can also set a variable where $date = <date in history>...

SELECT * FROM articles WHERE pubdate>=$date ORDER BY pubdate DESC;

 

[ScottW]

Sorry, didn't read your post more carefully....

SELECT pubdate FROM articles ORDER BY pubdate DESC LIMIT 1;

$lastdate=mysql_result($qry,0,"pubdate");

SELECT * FROM articles WHERE pubdate=$lastdate";

So, you would need to selects. I suppose there might be a way to do this with one lookup, using max(pubdate) or something like that. But your sub-select will not work in mysql.

 

[elander]

Hi All,
So the query should be something like this:

SELECT *
FROM articles
WHERE pubdate =
'SELECT pubdate
FROM articles
ORDER BY pubdate DESC
LIMIT 1'

If you are using MySQL 4.1 or later, you can use subselects. In case you're hosting yourself, I suggest upgrading to 4.1.12 or later.

The problem with your query is a simple one: use parenthesis, not single quotes!

SELECT * FROM articles
  WHERE pubdate =
    (SELECT pubdate FROM articles
      ORDER BY pubdate DESC
        LIMIT 1)

Have a look at this page from the documentation:
http://dev.mysql.com/doc/mysql/en/subqueries.html

If you're using an earlier version of MySQL, you can use JOINs instead of subqueries, have a look at this page from the documentation for an explanation:

http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html

 

[larry98765]

Aha! My version of MySQL is 4.0.21, so I will upgrade.

Many thanks to you both for helping me get to the bottom of this!

 

[elander]

Aha! My version of MySQL is 4.0.21, so I will upgrade.

Many thanks to you both for helping me get to the bottom of this!

De nada, help someone else here and we're even!