Results 1 to 13 of 13
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: Need a simple mysql query

    Hi

    I am newbie to mysql. I need a simple query for the below given scenario. Kindly help me.

    First Scenario

    ID | PID | Color | Size | Price | Status
    1 | 1 | Red | Medium | 2500 | N
    2 | 1 | Blue | Medium | 2500 | N
    3 | 1 | Red | Small | 1200 | N
    4 | 1 | Blue | Small | 1000 | N


    If All the status are 'N' then i need the result based on the lowest amount

    Expected Result

    ID | PID | Color | Size | Price | Status
    4 | 1 | Blue | Small | 1000 | N
    3 | 1 | Red | Small | 1200 | N

    --------------------------------------------------------------------

    Second Scenario

    ID | PID | Color | Size | Price | Status
    1 | 1 | Red | Medium | 2500 | N
    2 | 1 | Blue | Medium | 2500 | N
    3 | 1 | Red | Small | 1200 | Y
    4 | 1 | Blue | Small | 1000 | N


    I need the result based on the status, lowest amount

    Expected Result

    ID | PID | Color | Size | Price | Status
    3 | 1 | Red | Small | 1200 | Y
    4 | 1 | Blue | Small | 1000 | N


    --------------------------------------------------------------------


    Third Scenario

    ID | PID | Color | Size | Price | Status
    1 | 1 | Red | Medium | 2500 | N
    2 | 1 | Blue | Medium | 2500 | Y
    3 | 1 | Red | Small | 1200 | N
    4 | 1 | Blue | Small | 1000 | N


    I need the result based on the status, lowest amount

    Expected Result

    ID | PID | Color | Size | Price | Status
    2 | 1 | Blue | Medium | 2500 | Y
    3 | 1 | Red | Small | 1200 | N

    --------------------------------------------------------------------

  2. #2
    Join Date
    Dec 2013
    Posts
    14
    First Scenario
    select * from sample_data where status ='N' order by price

    Secon Scenario
    select * from sample_data where price in (select min(price) from sample_data group by status)

    Third scenario is not clear
    Assuming you need to take max(price) in the above query should get you the result

  3. #3
    Join Date
    May 2014
    Posts
    4
    Hi Abhinand,

    Thank you for your reply.But i Need a Single query which will satisfy all this three scenario.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rishibala View Post
    Thank you for your reply.But i Need a Single query which will satisfy all this three scenario.
    Why?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rishibala View Post

    Third Scenario

    ID | PID | Color | Size | Price | Status
    1 | 1 | Red | Medium | 2500 | N
    2 | 1 | Blue | Medium | 2500 | Y
    3 | 1 | Red | Small | 1200 | N
    4 | 1 | Blue | Small | 1000 | N


    I need the result based on the status, lowest amount

    Expected Result

    ID | PID | Color | Size | Price | Status
    2 | 1 | Blue | Medium | 2500 | Y
    3 | 1 | Red | Small | 1200 | N

    --------------------------------------------------------------------
    Why not take 2(Stauts = Y)and 4(lowest price)?
    ID | PID | Color | Size | Price | Status
    2 | 1 | Blue | Medium | 2500 | Y
    4 | 1 | Blue | Small | 1000 | N

  6. #6
    Join Date
    May 2014
    Posts
    4
    The mentioned three Scenario will occurs in the same table and at a same time.

    If Status is Y, Then i will give priority to the status and then the lowest price. I need to group the color.

    For one pid only one status will be Y, that either may be a lowest price or the highest price.

    If all the status are N. then i will look for the lowest price and i will group the color.
    Last edited by rishibala; 05-17-14 at 02:54. Reason: Added a few lines

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rishibala View Post
    The mentioned three Scenario will occurs in the same table and at a same time.
    So, it'll be better to put all three Scenario in one example by using different PID, like...

    Code:
    ID | PID | Color | Size   | Price | Status
    1  | 1   | Red   | Medium |  2500 | N
    2  | 1   | Blue  | Medium |  2500 | N
    3  | 1   | Red   | Small  |  1200 | N
    4  | 1   | Blue  | Small  |  1000 | N
    
    1  | 2   | Red   | Medium |  2500 | N
    2  | 2   | Blue  | Medium |  2500 | N
    3  | 2   | Red   | Small  |  1200 | Y
    4  | 2   | Blue  | Small  |  1000 | N
    
    1  | 3   | Red   | Medium |  2500 | N
    2  | 3   | Blue  | Medium |  2500 | Y
    3  | 3   | Red   | Small  |  1200 | N
    4  | 3   | Blue  | Small  |  1000 | N

    Expected Result:
    (1) If All the status are 'N' then i need the result based on the lowest amount (PID = 1)
    (2) I need the result based on the status, lowest amount (PID = 2, 3)

    Code:
    ID | PID | Color | Size   | Price | Status
    4  | 1   | Blue  | Small  |  1000 | N
    3  | 1   | Red   | Small  |  1200 | N
    
    3  | 2   | Red   | Small  |  1200 | Y
    4  | 2   | Blue  | Small  |  1000 | N
    
    2  | 3   | Blue  | Medium |  2500 | Y
    3  | 3   | Red   | Small  |  1200 | N
    Last edited by tonkuma; 05-17-14 at 03:18. Reason: Remove quesionable result.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rishibala View Post
    The mentioned three Scenario will occurs in the same table and at a same time.

    If Status is Y, Then i will give priority to the status and then the lowest price. I need to group the color.

    For one pid only one status will be Y, that either may be a lowest price or the highest price.

    If all the status are N. then i will look for the lowest price and i will group the color.
    Do you want
    to take a row with status Y or lowest price(if no status Y), for each PID and color?

  9. #9
    Join Date
    May 2014
    Posts
    4
    Quote Originally Posted by tonkuma View Post
    So, it'll be better to put all three Scenario in one example by using different PID, like...

    Code:
    2  | 3   | Blue  | Medium |  2500 | Y
    3  | 3   | Red   | Small  |  1200 | N      /* Right? */
    4  | 3   | Blue  | Small  |  1000 | N      /* Isn't this? */
    The ID 4 should not come, because i need to group the color. Others are exactly the same result i needed.

    Kindly provide me the mysql query !!!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    3 scenarios resolve to 3 simple queries
    3 scenarios within 1 query is not going to be a simpke query if its dooable at all. The best way I can think of meeting this requirement is via a stored procedure
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My two ideas.
    Not tested.

    Example 1:
    Code:
    SELECT b.*
     FROM  (SELECT pid , color
                 , COALESCE(
                      MAX(CASE status WHEN 'Y' THEN price END)
                    , MIN(price)
                   ) AS price
             FROM  @t
             GROUP BY
                   pid , color
           )  AS a
     INNER JOIN
           @t AS b
      ON   b.pid   = a.pid
      AND  b.color = a.color
      AND  b.price = a.price
     ORDER BY
           pid
         , status DESC
         , price  ASC
    ;

    Example 2:
    Code:
    SELECT b.*
     FROM  (SELECT pid , color
                 , ABS( MAX( CASE status
                             WHEN 'Y'    THEN   price
                             ELSE             - price
                             END ) ) AS price
             FROM  @t
             GROUP BY
                   pid , color
           )  AS a
     INNER JOIN
           @t AS b
      ON   b.pid   = a.pid
      AND  b.color = a.color
      AND  b.price = a.price
     ORDER BY
           pid
         , status DESC
         , price  ASC
    ;

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another one.
    Not tested.

    Example 3:
    Code:
    SELECT a.*
     FROM  @t AS a
     WHERE NOT EXISTS
           (SELECT 0
             FROM  @t AS b
             WHERE b.pid    = a.pid
              AND  b.color  = a.color
              AND
              (    b.status > a.status
               OR  b.status = a.status
               AND b.price  < a.price
              )
           )
     ORDER BY
           pid
         , status DESC
         , price  ASC
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by rishibala View Post
    ...

    Kindly provide me the mysql query !!!
    It must be one of an attitude for an issue.
    Though, I have another opinion.
    If complete answer was not provided,
    I prefer to try some solutions by myself, by utilizing suggestions, hints, partial solutions, so on ...(provided from other persons).
    And (I prefer to) publish my trials, then want to see other's opinions and/or trials/solutions suggested from that.


    Anyway, I also hope some MySQL experts to publish solutions for this issue(by good using of MySQL functionalities).
    It might bring me some good knowledges/insights for my understandings in SQL language and relational DBMS capabilities.
    Last edited by tonkuma; 05-17-14 at 12:14. Reason: Ammended singular/plural.

Posting Permissions

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