0

Is it possible to create view with row number ?

CREATE VIEW view_table
AS SELECT a.entity_id, a.firstname, a.lastname, 
b.vacation_start_day, b.vacation_end_day
FROM customer_info_table a, customer_vacations b

enter image description here

Robinio
  • 131
  • 9
  • What version of mysql? – Shawn Jul 18 '19 at 10:21
  • 2
    Possible duplicate of [Rank function in MySQL](https://stackoverflow.com/questions/3333665/rank-function-in-mysql) – Raymond Nijland Jul 18 '19 at 10:24
  • .... you can use RANK()/DENSE_RANK()/ROW_NUMBER() there depending of what you want to do with "ties" – Raymond Nijland Jul 18 '19 at 10:26
  • *Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard** `JOIN` syntax. – Gordon Linoff Jul 18 '19 at 10:27
  • 1
    ... but keep in mind that MySQL user variables do **not** work in [views](https://stackoverflow.com/questions/42021149/how-to-generate-sequence-number-in-mysql-view/42022625#42022625) if you don't have MySQL 8... You would have to use a co-related subquery with a count to simulate it which is slow.. – Raymond Nijland Jul 18 '19 at 10:27
  • `ROW_NUMBER()` window function avaible since MySQL 8.0. https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number – user14063792468 Jul 18 '19 at 10:27
  • I'm curious - what value would row number be to you? – P.Salmon Jul 18 '19 at 10:46
  • The entity id is repeated, and it interferes with my function, I need special values for each row in one column. – Robinio Jul 18 '19 at 11:45

0 Answers0