Trying to sort a table twice in one command.
-
I'm trying to sort a table in ascending LastName order and then move all the NEW names that have been added within the last month to the top of this search.
So that I get a table looking something like this:-
LastName DateAdded
Zachery 4th Nov 2005
Arthurs 1st Mar 2005
Bell 6th Apr 2003
Curtis 4th Jul 2004
Zachery would initially have been the last name in this table of four, but because he was added within the last month he gets moved to the top.
Meanwhile, the other three remain in alphabetical order because they've been there for longer than a month.
The reason I need this is so that I can list all the recent additions to the top of the table for a month, after which time it will resume it's alphabetical place at the end of the table.
Hope you can help
Alan
-
Alan you could use a case command e.g. using the pubs database this orders differntly depending on the date
SET DATEFORMAT dmy
select title_id, title, type, pubdate, orders=CASE
WHEN pubdate > '01/07/1991' THEN '1'
WHEN pubdate < '01/07/1991' THEN '2'
END
from titles
order by orders,
title
I put the case calsue in the select list in the example to be clear about what it does but it van be re-written thus
SET DATEFORMAT dmy
select title_id, title, type, pubdate
from titles
order by CASE
WHEN pubdate > '01/07/1991' THEN '1'
WHEN pubdate < '01/07/1991' THEN '2'
END,
title
hth
David
-
Two queries, one temp table and a reverse ranking by identity column!
1. Create a temp table for your data with the first column being an identity column.
2. Select the data into the table by name descending omitting records added within the last month
3. Select the data into the table with the ranking by date added and only the records added in the last month
4. Select the data out of the temp table sorting desc on the identity column and it should be in the desired order.
-
Modified from David's script
select LastName, DateAdded,
LastMonth = case when DateAdded >= convert(char(8), dateadd(month, -1, getdate()), 112) then
0
else
1
end
from #cust
order by LastMonth, LastName
or
select LastName, DateAdded
from #cust
order by case when DateAdded >= convert(char(8), dateadd(month, -1, getdate()), 112) then
0
else
1
end,
LastName
-
Excellent! Thanks guys.
As always, there's more than one way of 'skinning a cat'. I'm using the code below (based on the answers above) and it works exactly how I wanted it to
, BUT...... will someone explain to me what the CASE command actually does?SELECT *
FROM Celebs
ORDER BY CASE
WHEN DateAdded > GETDATE() - 30 THEN '1'
WHEN DateAdded < GETDATE() THEN '2'
END, LastName
Thanks again
Alan
-
Alan -
You may want to double check your logic unless you truly want records added more than 30 days ago, to the exact hour, minute and second to go into bucket '2'. "[KH]" is on the right track, you also may also need to think about the number of days in the month, since October has 31 days, 30 days ago was October 6 whereas 1 month ago was October 5th.
Try the following select statements stand alone in query analyzer to see the differences:
select getdate()-30
select dateadd(day,-30,getdate())
select dateadd(month, -1, getdate())
select cast(convert(char(8), dateadd(day,-30,getdate()), 112) as datetime)
select cast(convert(char(8), dateadd(month, -1, getdate()), 112) as datetime)
To use either of the last two statements in your case statement remove "select cast(" and "as datetime)", I just included to show what happens when SQL Server interprets the statements.
Joe
-
Thanks Joe,
I've tested your code in the Query Analyser and observed the subtle differences, and have adjusted my code accordingly.
Thanks to everyone.
Alan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply