CODEWITHSUNDEEP
mysqldate
Xeen
Xeen

Reputation: 7003

How to return SQL rows with the largest year from date field only?

So I have multiple fields in my database and one of which is date. The date field is of date datatype and I need to return only those values which have the largest year value within the date field.

So far I've done this:

$files = mysql_query("SELECT * FROM files WHERE unpublish=0 AND year(date) ORDER BY files.date DESC");

Now obviously it's not enough, how could I force it to treat year(date) to return only largest values?

Upvotes: 0

Views: 59

Answers (3)

user4482202
user4482202

Reputation:

One more option is to write like this:

$files = mysql_query("SELECT * FROM files
                      WHERE unpublish=0
                      AND date=(
                          SELECT YEAR(date)
                          FROM files
                          ORDER BY date DESC
                          LIMIT 1)
                      ORDER BY files.date DESC");

Or may be you want to select the max date from SELECT command like:

$files = mysql_query("SELECT col1, col2, col3, max(YEAR(date)) AS date
                      FROM files
                      WHERE unpublish=0
                      ORDER BY files.date DESC");

where col1, col2, col3 is manualy selected columns.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

Try this:

SELECT * FROM files 
WHERE unpublish=0 
and year(date)=(SELECT MAX(YEAR(date) FROM files)
ORDER BY files.date DESC

So it would be like

$files = mysql_query("SELECT * FROM files 
    WHERE unpublish=0 
    and year(date)=(SELECT MAX(YEAR(date) FROM files)
    ORDER BY files.date DESC");

Upvotes: 0

Raj
Raj

Reputation: 10853

SELECT * FROM files 
WHERE unpublish=0 
AND year(date)=(SELECT MAX(YEAR(date)) FROM files) 
ORDER BY files.date DESC

Upvotes: 2

Related Questions