Hi all

Hope everyone had a great Xmas!
Just a quick question :

On my website, people can write reviews about non-league football (soccer) clubs. The date they entered there review is stored in a database and so is the date of any `replies`. However, due to how the script works there is a tDATE column and a REPLYDATE column.
What I really need to happen is that when a user clicks on a link and they are taken to the review page, I need to show the NEWEST review OR newest REPLY being shown at the top of the page.
IE,

If a person posted an article yesterday and no one has posted any replies to that or any other article then that will be shown as the newest entry.

However, if someone `replies` today then I need that article to be shown at the top of the page as the `newest` entry simply because it has a reply.
Because Im `ordering by` the tDATE column first, can I order the REPLYDATE column also?
Ive tried :

sSQL="SELECT * FROM review WHERE INSTR(tleague,'" & nleague & "')ORDER BY tdate DESC, replydate DESC"

But the first posting it shows is one dated the 30th December 2008. However, Ive made a reply today and that is being shown as the second article when in fact I need that to be the first article to be shown.
I hope this all makes sense..

<

Database in use?<

Ahh sorry - forgot to add I'm using access .<

Sorry, you are using the same table for reviews and replies?<

The table 'review' holds both the original article plus the reply . I'm thinking of using just the 'tdate' column to store both dates (original posting and replies).


<

I can see potential problems doing that. If someone posts a message (topic) on Jan 1 at 10:00 am and a first reply is made at 10:02 am, the topic and reply will show the same date-time. However, if six months have gone by (with replies periodically throughout that time) you will find that having a topic and 400 replies with the same date/time will make it rather difficult to follow a thread - especially if replies occasionally refer to other topics or replies within other topics.

You would have to ignore the date/time for sorting (except for something like "active topics") and instead use the topic_id and reply_id within each message.<

Yes, I don't think your table design is the best option. You either use two different tables (probably the option I would prefer) or you just add an attribute that distinguishes articles and replies. Either of these will get you to a simple solution to the current problem and probably will solve other issues too. If you are going with a single table, then maintaining two date fields (one of them always empty) is simply bad table design.<