SortBy question

I have a table storing topics. Each topic contains 2 smalldatetime fields: "Created" and "LastReply". If the topic has no replies the "LastReply" field is NULL.

I need to make a SELECT statement that get the last activity. This means: I need to get the 5 last records that is created OR replied.

The question is how to do this? Is the solution to compare "Created" and "LastReply" for each record, and make a new field (called "LastActivity") with the "newest" date? Then, I need to sort the records by "LastActivity"?

The table is like this:

ID (int)
Title (varchar)
Created (SmallDateTime)
LastReply (SmallDateTime)


How to solve this? Hope someone please can help me!
LVL 1
webressursAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
you need this.
SELECT   * 
FROM     (SELECT *, 
                 CASE 
                   WHEN Created > LastReply THEN Created 
                   ELSE ISNULL(LastReply,Created) 
                 END AS LastActivity 
          FROM   your_table) AS t1 
ORDER BY LastActivity DESC

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Try this query
SELECT * FROM YourTable 
ORDER BY ISNULL(LastReply, Created) DESC

Open in new window


Raj
0
 
webressursAuthor Commented:
Thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.