Special order for 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 in accordance with the parameter provided by the user. This parameter indicates which entry will be first:

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, etc. Can you come up with a way to do this in SQL?

I use MySQL, but an example in any SQL database will do.

thanks

Answer 1

See how it works for you. Uses shared SQL, so it must be valid for MySql (unchecked).

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

: * , , . , "% maxrows" ORDER BY.

:

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 2

SQL?

, - , , .

.

Answer 3

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

: , :

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

Answer 4

, ( Oracle):

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
;

Answer 5

, , , :

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

.

Answer 6

SQL.

, .

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

, UNION ( ) ,

Answer 7

, LIMIT (ROW_NUMBER/OVER MySQL , , LIMIT ):

(
    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 greater than any number of results you may have.

I'm still not completely convinced that this will be faster in practice than reordering the list on the web server ... it will depend on how you dealt with the set of results and how big it was, I suppose. But at least this avoids the self-intersection involved in the beach smart approach.