CODEWITHSUNDEEP
mysqluniquedistinct
Stefan Mesaros
Stefan Mesaros

Reputation: 5

Select the most current records from multiple identical rows in the MySQL database

I am working on a product sample inventory system where I track the movement of the products. The status of each product can have a status of "IN" or "OUT" or "REMOVED". Each row of the table represents a new entry, where ID, status and date are unique. Each product also has a serial number.

I need help with a SQL query that will return all products that are currently "OUT". If I simply just select SELECT * FROM table WHERE status = "IN", it will return all products that ever had status IN.

Every time product comes in and out, I duplicate the last row of that specific product and change the status and update the date and it will get a new ID automatically.

Here is the table that I have: 

id | serial_number  | product | color  | date       | status
------------------------------------------------------------
 1 | K0T4N          | XYZ     | silver | 2016-07-01 | IN
 2 | X56Z7          | ABC     | silver | 2016-07-01 | IN
 3 | 96T4F          | PQR     | silver | 2016-07-01 | IN
 4 | K0T4N          | XYZ     | silver | 2016-07-02 | OUT
 5 | 96T4F          | PQR     | silver | 2016-07-03 | OUT
 6 | F0P22          | DEF     | silver | 2016-07-04 | OUT
 7 | X56Z7          | ABC     | silver | 2016-07-05 | OUT
 8 | F0P22          | DEF     | silver | 2016-07-06 | IN
 9 | K0T4N          | XYZ     | silver | 2016-07-07 | IN
10 | X56Z7          | ABC     | silver | 2016-07-08 | IN
11 | X56Z7          | ABC     | silver | 2016-07-09 | REMOVED
12 | K0T4N          | XYZ     | silver | 2016-07-10 | OUT
13 | 96T4F          | PQR     | silver | 2016-07-11 | IN
14 | F0P22          | DEF     | silver | 2016-07-12 | OUT

Upvotes: 0

Views: 55

Answers (2)

Shrikant Mavlankar
Shrikant Mavlankar

Reputation: 1153

This query will give you all the latest records for each serial_number

SELECT a.* FROM your_table a
LEFT JOIN your_table b ON a.serial_number = b.serial_number AND a.id < b.id
WHERE b.serial_number IS NULL

Below query will give your expected result

SELECT a.* FROM your_table a
LEFT JOIN your_table b ON a.serial_number = b.serial_number AND a.id < b.id
WHERE b.serial_number IS NULL AND a.status LIKE 'OUT'

Upvotes: 1

Willem Renzema
Willem Renzema

Reputation: 5187

There are two good ways to do this. Which way is best,in terms of performance, can depend on various factors, so try both.

SELECT
t1.*
FROM table t
LEFT OUTER JOIN table later_t
ON later_t.serial_number = t.serial_number
AND later_t.date > t.date
WHERE later_t.id IS NULL
AND t.status = "OUT"

Which column you check from later_t for IS NULL does not matter, so long as that column is declared NOT NULL in the table definition.

The other logically equivalent method is:

SELECT
t.*
FROM table t
INNER JOIN (
SELECT
serial_number,
MAX(date) AS date
FROM table
GROUP BY serial_number
) latest_t
ON later_t.serial_number = t.serial_number
AND latest_t.date = t.date
WHERE t.status = "OUT"

For each of these queries, I strongly suggest the following index:

ALTER TABLE table
ADD INDEX `LatestSerialStatus` (serial_number,date)

I use this type of query a lot in my own work, and have the above index as the primary key on tables. Query performance is extremely fast in such cases, for these type of queries.

See also the documentation on this query type.

Upvotes: 1

Related Questions