rank items in sql by dates

Question

I have movies table with movieID,movieName and movieDate. I want to select first all movies which added in last 24 hours and order them by date and the rest order by newid().

ie.

1 babylon 28/09/2010 16:00

2.12 monekys 28/09/2010 8:00

3.se7en 25/09/2010 5:00

4.snatch 26/09/2010 18:00

How can i achieve it?

Final answer which is a combination of all other answers which all do something good...

declare @date smalldatetime
set @date = dateadd(dd,-2,getdate())

DECLARE @d AS int
SET @d = DATEPART(ss, GETDATE()) + 3

   select
    movieID,
    movieTitle,
    movieDate
    from Movies
    order by 
        case 
            when movieDate>@date 
            then movieDate end desc, movieID % @d

Answer 1

Try something like (untested):

SELECT
  *
FROM (
  SELECT -- start with the newest movies
    movieID,
    movieName,
    movieDate,
    1 AS rnk -- new will need it to sort them property
  FROM
    movies
  WHERE
    movieDate >= DATEADD(h, -24, GETDATE()) -- from last 24 hours
  UNION ALL -- there will be no duplicates (so, so)
  SELECT
    movieID,
    movieName,
    movieDate,
    2 AS rnk -- goes after the newest ones
  FROM
    movies
  WHERE
    movieDate < DATEADD(hh, -24, GETDATE()) -- older then 24 hours
 ) AS movies
 ORDER BY
   rnk, 
   CASE WHEN rnk = 1 THEN movieDate ELSE newid() END -- order by movieDate newest movies and by newid() older ones

Are you sure you want to show all movies older then 24 hours?

Also probably trick with modulo on movieID column will be faster then using newid() function for sorting.

So instead of newid() something like:

DECLARE @d AS int
SET @d = DATEPART(ss, GETDATE()) + 3 -- to avoid division by 0

and then in ORDER clause instead of newid() use movieID % @d.

Answer 2

Another one to test.

ORDER BY 
  CASE WHEN movieDate >= DATEADD(hour, -24, GETDATE()) THEN movieDate END DESC,
  CHECKSUM(NEWID())

Answer 3

Something like this:

ORDER BY
    CASE
        WHEN [movieDate] BETWEEN DATEADD(d, -1, GETDATE()) AND GETDATE() 
            THEN [movieDate]    
        ELSE [movieID]
    END DESC    

Answer 4

Try

DECLARE @date AS datetime
SET @date = DateAdd(hh, -25, GetDate())

SELECT
  *
FROM (
  SELECT
    movieID,
    movieName,
    movieData,
    movieDate AS dateToOrder
  FROM
    movies
  WHERE
    movieDate >= DateAdd(h, -24, GetDate())
  UNION ALL
  SELECT
    movieID,
    movieName,
    movieData,
    @date AS dateToOrder
  FROM
    movies
  WHERE
    movieDate < DateAdd(hh, -24, GetDate()) -- older then 24 hours
 ) AS movies
 ORDER BY dateToOrder DESC, movieID