MySQL Forums
Forum List  »  Newbie

Re: Challenge while using row_number()
Posted by: Peter Brawley
Date: January 08, 2022 02:33PM

Taking your word for it doesn't supply actual ddl, which is what's needed to help with a solution. If this is not what you mean ...

drop table if exists table1,table2,table3,table4;
create table table1(id int,p int,points int);
create table table2 (
  pos int, id int,cp int,f1 int,f2 int,f3 int, f4 int,f5 int,f6 int,f7 int,gid int
);
create table table3(id int);
create table table4(gid int,r1 int); 
 
SELECT 
  row_number() over win as pos,
  f6, table2.id, (f7+ifnull(table1.p,0)) as cp, f1, f2, f3, f4, f5 
FROM table2
LEFT JOIN table3 ON (table2.id = table3.id)
LEFT JOIN table1 ON (table2.id = table1.id)
WHERE table2.gid = (SELECT max(gid) FROM table4 WHERE not(isnull(r1)))
WINDOW win AS (ORDER BY (f7+ifnull(table1.points,0)) desc, f1 desc, f2 desc, f3 desc, f4 desc, f5 desc) ;

... you'll need to provide usable DDL and a fuller, clearer explanation of the Order By logic you wish to add.

Options: ReplyQuote


Subject
Written By
Posted
Re: Challenge while using row_number()
January 08, 2022 02:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.