CODEWITHSUNDEEP
mysqlsqldate
Astha
Astha

Reputation: 1734

Mysql Date to timestamp SQL Query

I have release date in database as:

release_date
2010-12-02 00:00:00

and i have to get 50 rows from the data order by release date desc. Can anyone tell me how can i implement this in mysql sql query, some thing like

SELECT data.content,item.value
FROM contents 
 AS data 
JOIN items 
 AS item 
WHERE data.id=item.content_id 
ORDER BY data.release_date

please help, any idea will be highly appreciated.

EDIT

I am not asking how to limit it, but my problem is this query makes my script to run forever. Sorry for not clearing this before.

EDIT 2

contents
id | content | Comment | release_date

items
id | content_id | name | release_date

my both tables. Both have around 250000 rows

Upvotes: 0

Views: 749

Answers (5)

vicentazo
vicentazo

Reputation: 1809

Would be something like:

select data.content,item.value
from contents as data
   join items as item ON data.id=item.content_id
ORDER BY data.release_date

It's better to use ON clausule to join tables.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839184

It seems like you are most of the way there already. You missed DESC and you should add a LIMIT clause:

ORDER BY data.release_date DESC
LIMIT 50

Upvotes: 1

Ghazanfar Mir
Ghazanfar Mir

Reputation: 3541

Try this

$sql = "select data.content, item.value 
FROM contents as data 
JOIN items as item ON data.id=item.content_id 
ORDER BY data.release_date DESC
LIMIT 50";

Note the DESC for ordering in descending order, remove this clause and it will set to order in ascending order

Upvotes: 1

JNDPNT
JNDPNT

Reputation: 7465

same as you have but than: 

ORDER BY data.release_date DESC LIMIT 50;

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86476

Add the DESC and LIMIT in your existing query as like below

SELECT data.content,item.value
FROM contents 
 AS data 
JOIN items 
 AS item 
WHERE data.id=item.content_id 
ORDER BY data.release_date DESC
LIMIT 50

Upvotes: 2

Related Questions