Get Last First Inserted zero entry mysql

Question

Have three columns

ID  User     Quantity   Date
1    x        0       2016-01-01
2    x        2       2016-01-02
3    x        0       2016-01-03
4    x        0       2016-01-04
5    xx       0       2016-01-01
6    xx       2       2016-01-02
7    xx       0       2016-01-03
8    xx       8       2016-01-04
9    xx       0       2016-01-06
10   xx       0       2016-01-04

Now How do i get user wise first of the latest sequence of 0 entry for x user ID=3,xx ID=9.

No.. I need the query based on user name...Need all the users or singe user with their last first inserted zero record details — Manoj, Dec 02 '16 at 10:10
Please outline your question clearly, what exactly do you mean by "last first" ? — ColinM, Dec 02 '16 at 10:12
Guys, it's really not that hard to understand. The first of the latest sequence of 0 in quantity... — fancyPants, Dec 02 '16 at 10:21
For x user need to to find when quantity has become zero...For x user there are three zeros at ID 1,3,4 But at Id 2 we have > 0 quantity...So we need to neglect ID=1. rest 3,4 are there...in that first record we need to take that is Id = 3. — Manoj, Dec 02 '16 at 10:22
So what would "last first" it be for user "xx"? ID 7 or ID 9? They have the same date. Are you sequencing by date or by ID? — friedemann_bach, Dec 02 '16 at 10:25
@friedemann_bachConsider Id...there i have put some random date....its ID9 — Manoj, Dec 02 '16 at 10:27
http://stackoverflow.com/questions/30367982/how-to-know-the-date-of-insertion-of-record-in-sql — McNets, Dec 02 '16 at 10:28
@mcNets it will give Last one in the last sequence of zeros...Not first — Manoj, Dec 02 '16 at 10:31
Am expecting all the users with their ID,Name and date of first of the latest sequence of 0 in quantity... User,ID,Date x,3,2016-01-03 xx ,9, 2016-01-06 — Manoj, Dec 02 '16 at 10:45

score 1 · Answer 1 · edited Dec 02 '16 at 20:08

It's a bit complex, because we need to find the last not 0 entry for a name. (I would naturally tend to sort by date, but you said ID is what to go for.)

   SELECT t1.name, max(t1.id) as check_id FROM table t1 WHERE quantity > 0 GROUP BY name

Now you can get the lowest number for each name that is higher than the given check_id:

SELECT name, min(t2.id) FROM table t2
JOIN (SELECT t1.name, max(t1.id) as check_id FROM table t1 WHERE quantity > 0 GROUP BY name) as a ON a.name=t2.name
WHERE t2.quantity = 0 AND t2.id > a.id
GROUP BY t2.name

There's one problem. It will exclude everyone with only 0 quantity. If this could happen you need another query with the following code (to get only 0 values)

SELECT name, min(id) as check_id, sum(quantity) as qty_sum FROM table
GROUP BY name
HAVING qty_sum = 0

it works. And i keep your suggestion while posting the question. ty. — Manoj, Dec 02 '16 at 12:04
@Manoj Would you than please upvote my answer and mark it as an correct — Seb, Dec 02 '16 at 13:13

friedemann_bach · Answer 2 · 2016-12-02T11:41:10.303

As already mentioned, the first step is to identify the last dataset with a positive quantity by each user. The lowest ID of all following datasets would be what you seek. In other words, you need to look for MAX(ID) of all positive entries and apply MIN(ID) on all entries with a higher ID. In one query:

SELECT `User`, MIN(`ID`) FROM t `mainquery`
WHERE `ID` > (
  SELECT MAX(`ID`) FROM t `subquery`
  WHERE `Quantity` > 0 AND `mainquery`.`User` = `subquery`.`User`
  GROUP BY `User`
)
GROUP BY `User`

With the data from your example, this query returns:

User    MIN(`ID`)
x       3
xx      9

Test it here: http://sqlfiddle.com/#!9/3c487/1/0

Nitin · Answer 3 · 2016-12-02T11:49:10.063

0

Try This:

SELECT t1.user,min(t1.id) as ID,quantity,date from tablename t1
JOIN
(SELECT MAX(id) as qid,user FROM tablename WHERE Quantity>0 GROUP BY user) t2
ON t2.user=t1.user
WHERE t1.id>t2.qid
AND t1.Quantity=0
GROUP BY t1.user

edited Dec 02 '16 at 11:49

answered Dec 02 '16 at 11:37

Nitin

290
2
12

Get Last First Inserted zero entry mysql

3 Answers3