title: Mysql select ordinal
body: <p>Having a table named books which has the following structure:</p>
  
  <pre><code>╔════════════╦═══════════╦════════╗
  ║ LibraryId  ║  BookId   ║ Price  ║
  ╠════════════╬═══════════╬════════╣
  ║ 123        ║ 9001      ║ 10.99  ║
  ║ 123        ║ 9005      ║ 12.99  ║
  ║ 123        ║ 9006      ║ 7.99   ║
  ║ 124        ║ 8012      ║ 6.49   ║
  ║ 124        ║ 9001      ║ 3.19   ║
  ║ 124        ║ 9076      ║ 7.39   ║
  ╚════════════╩═══════════╩════════╝
  </code></pre>
  
  <p>How could I do a select that would return the full table, but additionally a field named Ordinal, that "counts" the number of books per library. The result should look something like:</p>
  
  <pre><code>╔════════════╦═══════════╦════════╦════════╗
  ║ LibraryId  ║  BookId   ║ Price  ║Ordinal ║
  ╠════════════╬═══════════╬════════╬════════╣
  ║ 123        ║ 9001      ║ 10.99  ║      1 ║
  ║ 123        ║ 9005      ║ 12.99  ║      2 ║
  ║ 123        ║ 9006      ║ 7.99   ║      3 ║
  ║ 124        ║ 8012      ║ 6.49   ║      1 ║
  ║ 124        ║ 9001      ║ 3.19   ║      2 ║
  ║ 124        ║ 9076      ║ 7.39   ║      3 ║
  ╚════════════╩═══════════╩════════╝════════╝
  </code></pre>
  
  <p>I have tried something like:</p>
  
  <pre><code>SET @var_record = 1;
  SELECT *, (@var_record := @var_record + 1) AS Ordinal
  FROM books;
  </code></pre>
  
  <p>But this will continue counting irrespective of the libraryId. I need something that will reset the ordinal every time the libraryId changes. I'd prefer a single query instead of procedures.</p>
  
  <hr>
  
  <p>Test data sql scripts:</p>
  
  <pre><code>create temporary table books(libraryId int, bookId int, price double(4,2));
  insert into books (libraryId, bookId, price) values (123, 9001, 10.99),(123, 9005, 10.99),(123, 9006, 10.99),(124, 8001, 10.99),(124, 9001, 10.99),(124, 9076, 10.99);
  </code></pre>