Special order by on SQL query

Question

I need to display a list of records from a database table ordered by some numeric column. The table looks like this:

CREATE TABLE  items (
  position int NOT NULL,
  name varchar(100) NOT NULL,
);
INSERT INTO items (position, name) VALUE
(1, 'first'),
(5, 'second'),
(8, 'third'),
(9, 'fourth'),
(15, 'fifth'),
(20, 'sixth');

Now, the order of the list should change according to a parameter provided by the user. This parameter specifies which record comes first like this:

position = 0
order should be = 1, 5, 8, 9, 15, 20

position = 1
order should be = 20, 1, 5, 8, 9, 15

position = 2
order should be = 15, 20, 1, 5, 8, 9

In other words the last record becomes the first and so on. Can you think of a way to do this in SQL?

I'm using MySQL but an example in any SQL database will do.

Thanks

Answer 1

ORDER BY (FIELD(position, 1, 5, 8, 9, 15, 20) + parameter) % 7

Edit: To make the peanut gallery happy, the general solution is:

ORDER BY (SELECT ix + parameter - 1 FROM (SELECT i.position, @ix := @ix + 1 AS ix FROM (SELECT @ix := 0) AS n, items AS i ORDER BY position) AS s WHERE s.position = items.position) % (SELECT COUNT(*) FROM items)

Answer 2

If it's a set list that you know the number of items you could do something like:

SELECT *
FROM Items
ORDER BY CASE WHEN Position >= Position THEN POSITION ELSE Position+1000 END

But its really ugly.

Answer 3

Are you sure you want to do this in SQL?

To me, this sounds like you should load the results in a dataset of some sort, and then either re-order them as you want, or position the starting point at the correct position.

Possibly using a linked list.

Answer 4

This really is not an ideal thing to be doing in SQL.

I have solution, but with large tables it will be slow.

DECLARE @iOrder INT
SET @iOrder = 4

SELECT abc.position,abc.name FROM
(
SELECT position,name,ROW_NUMBER() OVER (ORDER BY position) AS rownum
FROM items
) abc
WHERE abc.rownum >= @iOrder
UNION ALL
SELECT def.position, def.name FROM
(
SELECT position,name,ROW_NUMBER() OVER (ORDER BY position) AS rownum
FROM items
) def
WHERE def.rownum < @iOrder

Note that the use of UNION (Without the all) will reorder the results as it'll be looking for duplicates

Answer 5

See how this works for you. Uses generic SQL so it should be valid for MySql (untested) as well.

DECLARE @user_sort INTEGER
SET @user_sort = 0

SELECT position, name FROM 
(
  SELECT I1.position, I1.name, COUNT(*) AS rownumber, (SELECT COUNT(*) FROM items) AS maxrows
  FROM items I1, items I2
  WHERE I2.position <= I1.position
  GROUP BY I1.position, I1.name
) Q1
ORDER BY 
  CASE WHEN maxrows - rownumber < (@user_sort % maxrows) THEN 1 ELSE 2 END, position

Note: * If the user provided sort index is greater than the row count, the value will wrap to within the valid range. To remove this functionality, remove the "% maxrows" from the ORDER BY.

Results:

SET @user_sort = 0

position    name
1   first
5   second
8   third
9   fourth
15  fifth
20  sixth

SET @user_sort = 1

position    name
20  sixth
1   first
5   second
8   third
9   fourth
15  fifth

SET @user_sort = 2

position    name
15  fifth
20  sixth
1   first
5   second
8   third
9   fourth

SET @user_sort = 9

9   fourth
15  fifth
20  sixth
1   first
5   second
8   third

Answer 6

As per John's comment, but with LIMIT syntax instead (ROW_NUMBER/OVER doesn't work in MySQL and besides LIMIT is much easier to read):

(
    SELECT position, name FROM items
    ORDER BY position
    LIMIT @offset, @bignum
) UNION ALL (
    SELECT position, name FROM items
    ORDER BY position
    LIMIT @offset
)

Where @bignum is an arbitrary number higher than any number of results you might have.

I'm still not wholly convinced this will in practice be faster than rearranging the list on the web server... would depend exactly how you were dealing with the result set and how big it was, I suppose. But at least it avoids the self-cross-join involved in beach's clever approach.

Answer 7

I'm riffing on beach's solution here, but eliminating the self-join and only selecting from the items table twice (and using Oracle syntax):

select 
    i.position
  , i.name
from(
  select 
      items.*
    , ( SELECT COUNT(*) FROM items ) AS maxrows
  from items
  order by position
) i
order by 
  case 
    when rownum > maxrows - 2 -- NOTE: change "2" to your "position" variable
      then 1 - 1 / rownum -- pseudo-rownum < 1, still ascending
    else
      rownum
  end
;