How to check previous record in MySQL?


Problem :

+----+--------+-------------+
| ID |  Name  |  Category   |
+----+--------+-------------+
|  1 | Cat    | Animal      |
|  2 | Loin   | Animal      |
|  3 | Dog    | Animal      |
|  4 | Red    | Color       |
|  5 | Hen    | Bird        |
|  6 | Parrot | Bird        |
+----+--------+-------------+

I want Category wise serial number. that means output will be

+----+--------+----------+--------+
| ID |  Name  | Category | Count  |
+----+--------+----------+--------+
|  1 | Cat    | Animal   |      1 |
|  2 | Loin   | Animal   |      2 |
|  3 | Dog    | Animal   |      3 |
|  4 | Red    | Color    |      1 |
|  5 | Hen    | Bird     |      1 |
|  6 | Parrot | Bird     |      2 |
+----+--------+----------+--------+

Can anyone help ?



Solution :

Basically you need equivalent of ROW_NUMBER OVER(PARTITION), you can use self join and aggregation:

SELECT a.id, a.name, a.category, count(*) AS `Count`
FROM tab a
JOIN tab b 
  ON a.Category= b.Category AND a.ID >= b.ID
GROUP BY a.id, a.name, a.category;

LiveDemo

Output:

╔════╦════════╦══════════╦═══════╗
║ ID ║  Name  ║ Category ║ Count ║
╠════╬════════╬══════════╬═══════╣
║  1 ║ Cat    ║ Animal   ║     1 ║
║  2 ║ Loin   ║ Animal   ║     2 ║
║  3 ║ Dog    ║ Animal   ║     3 ║
║  4 ║ Red    ║ Color    ║     1 ║
║  5 ║ Hen    ║ Bird     ║     1 ║
║  6 ║ Parrot ║ Bird     ║     2 ║
╚════╩════════╩══════════╩═══════╝

Mysql Tutorials

Mysql Howto..

How to left join multiple one to many tables in mysql?

How to give a specific name to foreign key?

How do you use “WHERE x IN y” clauses with clojure/java.jdbc?

PHP: How to store HTML as it is in MySQL?

How to update mysql database from android

How to retrieve the most present pair in groups of three columns, with MySQL?

How should I store types of coupons in my db?

How to load a sub-application from a main application in Flex

How to normalize category database [closed]

How to return a MySQL array of results without using a PHP loop [closed]

How to encode fetched data from MySQL to JSON via PHP

Taking two tables, 1-to-many, how can I filter down the many table and then join ALL the matches in the 1 table?

How to join a table in symfony (Propel) and retrieve object from both table with one query

Weird MySQL query plan: why is this query using temporary & filesort? How to optimize it?

How to query MySQL if data in one field look like 32;13;3;33 and the condition is where 3

How to get image file name from MySQL record?

MySQL how to select all rows by specific day (mon, tue, etc) with datetime format

How do I bind a variable to a prepared statement with the SOCI libary?

How to make “column3” = “column1” - “column2” in SQL

How can I optimize my query (rank query)?

How to delete last record(on condition) from a table in MySql

How to abbreviate MySQL queries in PHP?

How to get distinct record from mysql table?

How can I see how long statements take to execute on the mysql command line?

how write update and inner query for multiple records in msql

MYSQL group BY Date count how many [closed]

How to submit a timestamp to a MySQL database

How to select 3 row per category in MySQL?

how to write a script for connecting to mysql by TCP/IP over SSH?

CGI using MySQL: How to display all data from table?