Question 374846 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a way to set a column in mysql that will automatically be the value of the row's row number?

I’m very new to creating my own projects and SQL in general and I’m having issues with having a "rank" column that will generate the ranking of a specific row of data based on the "time" column. I’m making a full-stack CRUD database which pulls from a mysql database to an express front end, and so far I can’t seem to figure out how to do a "search" function that returns the row where name = ? from user input while using the ROW_NUMBER function. I still think that having an entirely separate column would be ideal compared to running the ROW_NUMBER query every time I want to find the search result, but I have no clue how I would do that.

So far my search function is as follows:

async searchByName(name) {
        try{
            const response = await new Promise((resolve, reject) => {
                const query = "SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn FROM medley_relay; SELECT * FROM medley_relay WHERE name = ?;";

                connection.query(query, [name], (err, results) => {
                    if (err) reject(new Error(err.message));
                    resolve(results);
                })
            });
            console.log(response);
            return response;
        } catch (error) {
            console.log(error);
        }
    }

It is called like so:

    const {name} = request.params;
    const db = dbService.getDbServiceInstance();

    const result = db.searchByName(name);

    

    result
    .then(data => response.json({data : data}))
    .catch(err => console.log(err)); 
    console.log(result);
})

and sent to the front end with this:


searchBtn.onclick = function() {
    const searchValue = document.querySelector('#search-input').value;

    fetch('http://localhost:5000/search/' + searchValue)
    .then(response => response.json())
    .then(data => loadHTMLTable(data['data']));
}

loadHTMLTable is

    const table = document.querySelector('table tbody');

    console.log(data);

    if (data.length === 0) {
        table.innerHTML = "<tr><td class='no-data'>No Data</td><td class = 'no-data'colspan='5'>No Data</td></tr>";
        return;
    }

    let tableHtml = "";

    data.forEach(function ({id, name, time, date, rn}) {
    
        tableHtml += "<tr>";
        tableHtml += `<td>${rn}</td>`;
        tableHtml += `<td>${name}</td>`;
        tableHtml += `<td>${time}</td>`;
        tableHtml += `<td>${new Date(date).toLocaleDateString()}</td>`;
        tableHtml += `<td><button class="delete-row-btn" data-id=${id}>Delete</td>`;
        tableHtml += `<td><button class="edit-row-btn" data-id=${id}>Edit</td>`;
        tableHtml += "</tr>";
        
    });

    table.innerHTML = tableHtml;

}

where I have a user input for the searched name as an html element on my web app.

What I get in return is simply an undefined table for all elements, and when I try to console.log the response I get:

  [
    RowDataPacket {
      id: 15,
      name: 'Corning',
      time: '01:40:15',
      date: 2024-04-10T04:00:00.000Z,
      rn: 1
    },
    RowDataPacket {
      id: 16,
      name: 'Binghamton',
      time: '01:52:42',
      date: 2024-04-02T04:00:00.000Z,
      rn: 2
    }
  ],
  [
    RowDataPacket {
      id: 16,
      name: 'Binghamton',
      time: '01:52:42',
      date: 2024-04-02T04:00:00.000Z
    }
  ]
]

Again I apologize if this is something that is really easy as its my first time using SQL or PHPmyadmin, but being able to have a dedicated column to being the row’s number that will automatically refresh when entries are added or deleted would be great. So far all of the other things I’ve found rely on ROW_NUMBER which doesn’t set up a permanent column, at least not for me, which wouldn’t work as my "rank" ends up being undefined for my table.

Tags:

2

Answers


  1. 0

    No, there isn’t an automatic persistent row-number column possible.

    There are several reason for this:

    • If you insert or delete a single row in a table with 1 billion rows, do you really want the overhead of updating the row number on a billion rows? Most users would say no. It would make simple operations into very costly ones.

      Also if you use a binary log in ROW based format, it would copy every row updated into the log.

    • MySQL supports concurrent transactions, so row numbers may be different for each session. If I’m inserting some rows, but I haven’t committed my new rows yet, should the table re-number the rows? Perhaps in my session, but not in yours, where you are in a transaction that is viewing the old state of the table. You may even have inserted or deleted different rows in your session, which I my session can’t see.

    • Any query can include JOIN or UNION, or exclude rows from a given result based on conditions in the WHERE clause, or aggregate multiple rows into fewer rows based on GROUP BY expressions. All these can change the row numbers for a given query result.

    Login or Signup to reply.
  2. 0

    Assuming time has an index, and you are only expecting to get a small subset of the table rows, you can most easily calculate a rank like:

    select mr.*, 1+(select count(*) from medley_relay mr2 where mr2.time < mr.time) 'rank'
from medley_relay mr
where mr.name=?

    (Have a time index on the table to be efficient.)

    This produces an equivalent to rank(), not row_number() – ties will get the same rank. If you want row_number instead and have a primary key pkid, do where (mr2.time,mr2.pkid) < (mr.timr,mr.pkid). For the equivalent of dense_rank(), change to count(distinct mr2.time).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search