SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Difficult problem in output query

    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:
    Code:
    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:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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?
    I need expert opinion.

    Thank you in advance for your help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    ... that I don't think you can in MySQL and in general in SQL.
    sure you can

    you just haven't described how to decide which rows you actually want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | idticket | d1_assignation   | resource | state        | statenotebook          | id      | id2   |
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 36776190 | 2013-03-17 10:13 |          | Assigned     |                        | 4070608 | 35018 |
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    between these two lines:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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:
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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):
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | idticket | d1_assignation   | resource | state        | statenotebook          | id      | id2   |
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 36781730 | 2013-03-18 07:56 | PES/AT   | Assigned SMS | transfered to notebook | 4080832 | 43873 |
    +----------+------------------+----------+--------------+------------------------+---------+-------+

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 ...
    Code:
    +----------+------------------+----------+--------------+------------------------+---------+-------+
    | 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 |
    +----------+------------------+----------+--------------+------------------------+---------+-------+


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •