Solved

SortBy question

Posted on 2011-03-19
3
324 Views
Last Modified: 2012-08-14
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!
0
Comment
Question by:webressurs
3 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35173373
Try this query
SELECT * FROM YourTable 
ORDER BY ISNULL(LastReply, Created) DESC

Open in new window


Raj
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35174018
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
 
LVL 1

Author Closing Comment

by:webressurs
ID: 35179201
Thanks :)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need some help to cast ntext to nvarchar SQL 2000 7 37
TSQL - How to declare table name 26 41
Mysql Left Join Case 10 70
Loops and updating in SQL Query 9 52
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question