CODEWITHSUNDEEP
sqlmysqlsql-order-by
Bob Bright
Bob Bright

Reputation:

Special order by on SQL query

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

Upvotes: 3

Views: 2453

Answers (7)

Alkini
Alkini

Reputation: 1479

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
;

Upvotes: 1

bobince
bobince

Reputation: 536339

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.

Upvotes: 0

chaos
chaos

Reputation: 124267

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)

Upvotes: 1

beach
beach

Reputation: 8630

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

Upvotes: 2

John
John

Reputation: 5834

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

Upvotes: 0

Bravax
Bravax

Reputation: 10483

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.

Upvotes: 2

Ryan Roper
Ryan Roper

Reputation: 333

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.

Upvotes: 0

Related Questions