Newest Date

[The Little Guy]

I have a timestamp column, how can I get the timestamp with the closest time to the current time?

 

Example:

 

Now: 2008-10-31 15:23:00

 

In database:

2008-10-30 14:25:00

2008-10-29 08:54:00

2008-10-31 14:32:00

2008-10-29 22:10:00

 

The closest one to Now would be the 3rd one.

[jwilliam]

Maybe something like this:

 

SELECT * FROM table ORDER BY timestamp DESC LIMIT 1;

 

 

[The Little Guy]

I don't think that is what I am after, because I don't really want to order by the timestamp, I just want to select the largest one...

[Flames]

you cant have a largest timestamp, and its not ordering all the timestamps, its showing them all from your table ordering by timestamp descending (e.g. latest date first) then limiting itself to only get 1 result so it only ever gets 1 result as you put it the largest one.

[The Little Guy]

Then how would yo integrade it into this query? It needs to be the newest from either the table: topics or replies.

 

SELECT c.`category`,
c.id AS 'cid',
b.order,
b.`title` AS 'title',
b.`description` AS 'description',
b.id 'bid',
COUNT(t.id) AS 'topicCount',
COUNT(r.id) AS 'postCount'
FROM `boards` b
LEFT JOIN topics t ON(t.`boardID` = b.id)
LEFT JOIN replies r ON(r.`topic_id` = t.id)
LEFT JOIN categories c ON(b.`group` = c.id)
GROUP BY b.`id`
ORDER BY
c.`order` ASC, b.`group` ASC, c.`order` ASC

[xtopolis]

TLG, what are you trying to accomplish in relation to the forum you're building?

 

Is this to pull the title of the last post to display in the "Last post info" area?

 

Also, it would help me if you gave me a data dump (not the schema, but actual data export as well) and tell me what columns to display and what conditions). .. mainly data to work with.

 

I have a feeling it will be an additional column in that query that's something like: select max(post_date) from replies where topic id in topic.id or w/e

[The Little Guy]

I just want to grab what the last post date of the item is, such as in the forum...

 

"on Today at 12:24:24 PM"

 

That shows the date and time of the last post or topic. I need to grab that date.

 

I have tried a MAX, and it just displays as "1"... maybe I was doing it wrong.

[xtopolis]

In a straight single column query, it returns the appropriate timestamp as you showed in the top post.

 

Give us some sql of your structure + data if you can't get it.