1

I am trying to create a query that will shift the results on each execution for each user on my website. Goal to accomplish this using SQL Server 2008 database technology.

For example if I have a table with three rows of data:

I am trying to find a way to select this data order by name ascending then shifting/rotating the result.

ID  Name 
1   Apple 
2   Orange 
3   Banana

User 1 loads a page and sees

Apple
Banana
Orange

User 1 reloads the same page and then sees

Banana
Orange
Apple

User 1 reloads the same page and then sees

Orange
Apple
Banana

User 2, etc have the exact same experience, rotating/shifting of the results in the same order.

I can create and store any values necessary such as the first or last name or ID.

Is there a simple elegant solution (query, stored procedure, function, etc) that will accomplish this task at the SQL Server level?

The reason being is to give all colors the opportunity to be displayed at the top of the list vs alphabetical ordered A to Z.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sir Force
  • 28
  • 8

2 Answers2

1

If SQL Server had sequences and a nextval() function like PostgreSQL or Oracle it could be as simple as that - given your base sort were on id, starting at 1, without holes:

SELECT id, name
  FROM mytbl
 ORDER BY CASE WHEN id > @nextval%number_of_rows THEN 0 ELSE 1 END, rn

See here for workarounds: Oracle sequence but then in MS SQL Server.

Edit with new input from question

It could look like this then:

DECLARE @nextval int;

WITH x AS
(
SELECT id
      ,name
      ,ROW_NUMBER() OVER(ORDER BY name) AS rn
      ,COUNT(*) OVER() AS ct
  FROM mytbl 
)
SELECT id
      ,name
  FROM x
 ORDER BY CASE WHEN rn > @nextval%ct THEN 0 ELSE 1 END, rn
  • Sort alphabetically.
  • Number of rows dynamic.
  • Syntax is tested now
  • You only need a way to increment @nextval. The above link may help.

To get my syntax straight, I wrote a demo on data.stackexchange.com
You can go there and check it out!

This would be simpler if SQL Server had something like LIMIT and OFFSET. TOP can't do the job. See: Equivalent of LIMIT and OFFSET for SQL Server?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the idea. Ultimately my example is simplified. Ideally I would be alphabetically sorting the names and rotating/shifting. I will update the original question to indicate that. – Sir Force Oct 10 '11 at 21:25
  • Works for me, I can actually store and increment a number for each user to keep their place that I initialize to 0 for the first execution. Thank you very much! – Sir Force Oct 11 '11 at 03:14
0

I don't know about shift or rotate output of SQL server query, but I'm trying to do it same you, and I use NEWID() to order random my outputs.

select name,company,etc from tblCompany order by NEWID() desc
David Buck
  • 3,752
  • 35
  • 31
  • 35
user3150241
  • 31
  • 8
  • Interesting idea, but at the time I needed to specifically rotate ads, in reality I needed to do this on a per user basis to ensure equal display time. Thanks for resurrecting this question and on my 50th bday! Cheers! – Sir Force May 15 '20 at 03:07