Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query with time sorting...

Author  Topic 

NewMedia42
Starting Member

35 Posts

Posted - 2014-03-08 : 18:10:34
I have an interesting problem - right now I have a table which has a pool of things that need to be done. It's important that these things NOT be done in insert order, so right now I simple do an "ORDER BY newid()" so I get some random value. The problem with this is that we still need things done in a somewhat time sensitive way - meaning that the older something is, the more likely it would be to be selected.

I can get around this manually in the application, by just getting the oldest item also, then using some weighted value for oldest vs random - but my question is, is there some alternative method I could use, other than ORDER BY newid(), that would still give random returns, but could also use a timedate column to give precedence to older item.

Thanks for any insight anyone has!

k_a_f_k_a
Starting Member

2 Posts

Posted - 2014-03-09 : 07:59:58
Something like this perhaps?
select *
from mytable
order by datediff(d, mytimedatecolumn, getdate()) + abs(checksum(NEWID())) % 1000 desc
You can in/decrease that number, depending on how much variance you want in your results.
Go to Top of Page
   

- Advertisement -