Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SortBy question

Posted on 2011-03-19
3
Medium Priority
?
331 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 41

Accepted Solution

by:
Sharath earned 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 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