I thought that using a view or a table or a temporary table would give me the same result.
I'm creating some output to be used in another statement that works with CREATE TABLE or CREATE TEMPORARY TABLE but does not work with CREATE VIEW.
The output I'm creating uses fields from 3 joined tables. The interesting part is a new field I'm generating in the output using IFs and @vars to add an ordinal number within a group. Here's some sample data of the output w/o the ordinal field:
display_name date_played end_hcp
Alan 2017-08-23 720
Alan 2017-08-29 692
Alan 2017-09-26 694
Bill 2017-08-24 865
Bill 2017-09-02 868
Bill 2017-09-21 842
Dave 2017-08-24 363
Dave 2017-08-31 339
Dave 2017-09-05 332
Dave 2017-09-15 348
Dave 2017-09-17 374
And here's what I want it to look like with the new Ordinal column added in:
display_name date_played end_hcp Ordinal
Alan 2017-08-23 720 1
Alan 2017-08-29 692 2
Alan 2017-09-26 694 3
Bill 2017-08-24 865 1
Bill 2017-09-02 868 2
Bill 2017-09-21 842 3
Dave 2017-08-24 363 1
Dave 2017-08-31 339 2
Dave 2017-09-05 332 3
Dave 2017-09-15 348 4
Dave 2017-09-17 374 5
Here's the working code using a CREATE TEMPORARY TABLE:
DROP TEMPORARY TABLE IF EXISTS pre_ordinal;
CREATE TEMPORARY TABLE pre_ordinal AS
SELECT u.display_name,
m.date_played,
p.end_hcp
FROM `lwljhb_lwl_matches` AS m
JOIN `lwljhb_lwl_players` AS p
JOIN `lwljhb_users` AS u
ON m.id = p.match_id AND
p.player_id = u.id
WHERE league_seasons_id = 12 AND
playoff_round = 0
ORDER BY u.display_name, m.date_played;
SELECT po.*,
@rn := if ( @display_name = po.display_name,
@rn + 1,
if ( @display_name := po.display_name, 1, 1)
) as Ordinal
FROM pre_ordinal AS po
JOIN (select @rn := 0, @display_name:=null) x
ORDER BY po.display_name, po.date_played;
If I use a CREATE OR REPLACE VIEW the Ordinal column is nearly all 1's. I noticed that in each of the very few times I got an Ordinal >1 it was when the ids for the match table were consecutive (the records were in physical order?).
I want to use my SQL within a WordPress plugin to create a chart. The plugin for some reason won't work with multiple statements, so the DROP TABLE; CREATE TABLE approach doesn't fly, whereas CREATE OR REPLACE VIEW is only one statement and works, it just gets the wrong answer.
Why would this be?
https://dev.mysql.com/doc/refman/5.7/en/create-view.html says in part:
ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.
Apparently, the truth about ORDER BY and views may be that it always ignores the ORDER BY.
Since the order of evaluation is crucial in your query, I don't think it's reliable to use a view.