Hello guys.
I just discussed with my boss because ask me something that I don’t think you can in MySQL and in general in SQL.
I’ve this output:
mysql> SELECT
*
FROM
doTable
WHERE
`idticket` LIKE '%36781730%'
OR `idticket` LIKE '%36776190%';
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:12 | Prep | Assigned SMS | transfered to notebook | 4070607 | 35017 |
| 36776190 | 2013-03-17 10:13 | | Assigned | | 4070608 | 35018 |
| 36781730 | 2013-03-18 07:52 | | Assigned | | 4080831 | 43872 |
| 36781730 | 2013-03-18 07:56 | PES/AT | Assigned SMS | transfered to notebook | 4080832 | 43873 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
4 rows in set
what you ask instead is:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:12 | Prep | Assigned SMS | transfered to notebook | 4070607 | 35017 |
| 36781730 | 2013-03-18 07:52 | | Assigned | | 4080831 | 43872 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
to my knowledge the two lines have nothing in common.
For the lines with id2 35017 and id2 43873, is sufficient insert the condition ‘resource’ NOT NULL and ‘state’ equal to ‘Assigned’, but this condition not extract the lines with id2 43872 and id2 35018, because the ‘resource’ IS NULL.
It’s correct what I say?
Thank you in advance for your help.
r937
March 19, 2013, 4:17pm
2
sure you can
you just haven’t described how to decide which rows you actually want
it’s a good surprise for me, thank you.
between these two lines:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:12 | Prep | Assigned SMS | transfered to notebook | 4070607 | 35017 |
| 36776190 | 2013-03-17 10:13 | | Assigned | | 4070608 | 35018 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
I need extract this row:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:12 | Prep | Assigned SMS | transfered to notebook | 4070607 | 35017 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
because in the next row with the same ‘idticket’ the value of field ‘resource’ is null:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:13 | | Assigned | | 4070608 | 35018 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
between these two lines:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36781730 | 2013-03-18 07:52 | | Assigned | | 4080831 | 43872 |
| 36781730 | 2013-03-18 07:56 | PES/AT | Assigned SMS | transfered to notebook | 4080832 | 43873 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
I need extract this row:
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36781730 | 2013-03-18 07:52 | | Assigned | | 4080831 | 43872 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
because though in the next row (# 43873) with the same ‘idticket’ the value of field ‘resource’ is not null, I’ve ‘datetime’ lower in previous row (# 43872):
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36781730 | 2013-03-18 07:56 | PES/AT | Assigned SMS | transfered to notebook | 4080832 | 43873 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
r937
March 19, 2013, 5:09pm
4
okay, so you need to reduce all of the above to a simple statement
example: for all rows with the same ticket, return the row with the latest datetime where the resource is not null
is that correct?
r937:
okay, so you need to reduce all of the above to a simple statement
example: for all rows with the same ticket, return the row with the latest datetime where the resource is not null
is that correct?
Is partially corrected, because for row with id2 # 43872 I’ve the ‘resource’ is null and datetime not is latest, but penultimate …
+----------+------------------+----------+--------------+------------------------+---------+-------+
| idticket | d1_assignation | resource | state | statenotebook | id | id2 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
| 36776190 | 2013-03-17 10:12 | Prep | Assigned SMS | transfered to notebook | 4070607 | 35017 |
| 36781730 | 2013-03-18 07:52 | | Assigned | | 4080831 | 43872 |
+----------+------------------+----------+--------------+------------------------+---------+-------+
