I've got following, simple table
Item (id, name, date, fixed_position)
(1, 'first entry', '2016-03-09 09:00:00', NULL)
(2, 'second entry', '2016-03-09 04:00:00', 1)
(3, 'third entry', '2016-03-09 05:00:00', NULL)
(4, 'fourth entry', '2016-03-09 19:00:00', NULL)
(5, 'fifth entry', '2016-03-09 13:00:00', 4)
(6, 'sixth entry', '2016-03-09 21:00:00', 2)
The number of items is not fixed, in fact can vary from ~100 to ~1000.
What i want to achieve is to perform a query to return set of Items ordered by date field which takes into consideration fixed_position field, which stands for something like "pinned" results to specific positions. If fixed_position for given entry is not NULL the result should be pinned to n-th position and if fixed_position is NULL the ORDER BY should take precedence.
Desired output of query for brighter explanation:
(2, 'second entry', '2016-03-09 04:00:00', 1) // pinned to 1-st position
(6, 'sixth entry', '2016-03-09 21:00:00', 2) // pinned to 2-nd position
(3, 'third entry', '2016-03-09 05:00:00', NULL) // ORDER BY `date`
(5, 'fifth entry', '2016-03-09 13:00:00', 4) // pinned to 4-th position
(1, 'first entry', '2016-03-09 09:00:00', NULL) // ORDER BY `date`
(4, 'fourth entry', '2016-03-09 19:00:00', NULL) // ORDER BY `date`
I've tried solution posted in Ordering MySql results when having fixed position for some items but even with copy-paste method this doesn't seem to work at all.
What I've tried this far is this query:
SELECT
@i := @i +1 AS iterator,
t.*,
COALESCE(t.fixed_position, @i) AS positionCalculated
FROM
Item AS t,
(
SELECT
@i := 0
) AS foo
GROUP BY
`id`
ORDER BY
positionCalculated,
`date` DESC
Which returns:
iterator | id | name | date | fixed_position | positionCalculated
1 1 first entry 2016-03-09 09:00:00 NULL 1
2 2 second entry 2016-03-09 04:00:00 1 1
6 6 sixth entry 2016-03-09 21:00:00 2 2
3 3 third entry 2016-03-09 05:00:00 NULL 3
4 4 fourth entry 2016-03-09 19:00:00 NULL 4
5 5 fifth entry 2016-03-09 13:00:00 4 4
Does MySQL can perform such task or should I take backend approach and perform PHP's array_merge() on two result sets?
A brute force method to solve this would be to first create a tally table having an amount of rows bigger than the original table:
SELECT @rn := @rn + 1 AS rn
FROM (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
CROSS JOIN (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
CROSS JOIN (SELECT @rn := 0) AS v
Then you can left join this table to a derived table containing all fixed positions of your original table:
SELECT Tally.rn
FROM (
... tally table query here
) AS Tally
LEFT JOIN (
SELECT fixed_position
FROM Item
) AS t ON Tally.rn = t.fixed_position
WHERE t.t.fixed_position IS NULL
The above returns the to-be-filled missing order positions.
You can now use the above query as yet another derived table joined to the original table to achieve the desired ordering:
SELECT id, name, `date`, fixed_position, Gaps.rn,
derived.seq, Gaps.seq
FROM (
SELECT id, name, `date`, fixed_position,
@seq1 := IF(fixed_position IS NULL, @seq1 + 1, @seq1) AS seq
FROM Item
CROSS JOIN (SELECT @seq1 := 0) AS v
ORDER BY `date`
) AS derived
LEFT JOIN (
SELECT Tally.rn,
@seq2 := @seq2 + 1 AS seq
FROM (
SELECT @rn := @rn + 1 AS rn
FROM (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
CROSS JOIN (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
CROSS JOIN (SELECT @rn := 0) AS v
) AS Tally
LEFT JOIN (
SELECT fixed_position
FROM Item
) AS t ON Tally.rn = t.fixed_position
CROSS JOIN (SELECT @seq2 := 0) AS v
WHERE t.t.fixed_position IS NULL
ORDER BY rn
) AS Gaps ON (derived.seq = Gaps.seq) AND (derived.fixed_position IS NULL)
ORDER BY COALESCE(derived.fixed_position, Gaps.rn)
I've had the same problem (sort by date + inject rows with fixed position values). The above solution seems to work. But you have to know how much values your table has. The line:
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
has to get extended if you have more rows because the temporary table is to short. After some google search results and tests in my DB I've figured out a solution that fits your needs and is smarter to understand I think.
SELECT * FROM (SELECT create_date, fixedposition, @rownum:=@rownum + 1 AS colposition, 1 AS majorEntry FROM myTable JOIN (SELECT @rownum:=0) r WHERE fixedposition IS NULL ORDER BY crdate DESC) AS orderedFixed UNION ALL (SELECT create_date, fixedposition, fixedposition AS colposition, 0 AS majorEntry FROM myTable WHERE fixedposition IS NOT NULL ORDER BY fixedposition ASC) ORDER BY colposition ASC, majorEntry
So this is how it works: There are two SELECT statements. The first SELECT searches for all columns without a fixed position and sorts them by date. Additionally it makes a JOIN to add a row counter column. The second one searches for all rows with a fixed position and returns the sorting by "colposition".
Both select statements gets combined by an UNION ALL. The union gets sorted first by the ASCending colposition and in the second step by the majonEntry-value which indicates that the rows with fixedposition are to be placed before the other rows which have the same position.