SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Eliminate duplicate rows

    This script:
    Code:
    SELECT 
    		plan.*, trkg.curwipprice_online, trkg.addr 
    	FROM 
    		plan 
    	LEFT 
        JOIN	
    		trkg 
    	ON 
    		plan.cliorder = trkg.cliorder 
    	AND trkg.addr = '$addr_clear'	
    	WHERE 	
    		(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
    	ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC
    Produces this output:

    Item Name.............Price
    USS Constitution ........1.75
    USS Constitution ........7.55
    Peterbilt 353 ........94.92
    Wonder Woman........1.65
    Wonder Woman........14.22
    H.M.S. Beagle ........31
    H.M.S. Beagle ........33.77

    How do I eliminate the dupes and keep the item_names with the lowest price with SQL or do I need to do that with PHP?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can do it with php as long as the number of returned rows is small

    you can also do it with an additional subquery in the FROM clause

    why LEFT JOIN? do you want to show all plans, even those without tracking?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, I need all the plan data.

    What does a query inside the FROM look like?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    What does a query inside the FROM look like?
    Code:
    SELECT ...
      FROM plan 
    LEFT OUTER 
      JOIN ( SELECT cliorder
                  , MIN(curwipprice_online) AS lowest
               FROM trkg
              WHERE addr = '$addr_clear' 
             GROUP
                 BY cliorder ) AS x
        ON x.cliorder = plan.cliorder
    LEFT OUTER
      JOIN trkg 
        ON trkg.cliorder = x.cliorder
       AND trkg.curwipprice_online = x.lowest
       AND trkg.addr = '$addr_clear'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Very cool r937!

    I'm following you, but I created two unintended consequences in my OP.

    I didn't state in my OP that I also need the unmatched records from the table called plan. Turns out that deleting the second LEFT JOIN resolved that issue. I also changed "MIN(curwipprice_online) AS lowest" to "MIN(curwipprice_online) AS curwipprice_online" in an attempt to match the output format in the OP. Here's the current query:
    Code:
    SELECT 
    		*
    	FROM 
    		plan
    	LEFT OUTER JOIN 
    		(SELECT 
    			cliorder, MIN(curwipprice_online) AS curwipprice_online
            FROM 
    			trkg
            WHERE 
    			addr = '$addr_clear' 
            GROUP
                BY cliorder 
    		) AS x
        ON 
    		x.cliorder = plan.cliorder
    The other unintended consequence is that I need each row to produce an array that ends with these keys (see my OP):
    [68]=> string(4) "0.47" ["curwipprice_online"]=> string(4) "0.47"
    [69]=> string(14) "1840 Pawnee St" ["addr"]=> string(14) "1840 Pawnee St"

    The current query selects rows that produce an array that ends with:
    [68]=> NULL
    [69]=> NULL ["curwipprice_online"]=> NULL }

    I don't think the order matters as much as that missing key.

    How do I get the ["addr"] key from the table called trkg, into the selection results? I tried adding ", addr" in the second select, but nogo.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    How do I get the ["addr"] key from the table called trkg, into the selection results?
    by restoring the join you removed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

    When I do that I loose the item_names on the unmatched plan rows.

    To confirm, I need all the columns from all the rows from plan that qualify on the WHERE in the OP plus the curwipprice_online and addr columns appended at the end of the plan columns on the plan rows that match the trkg rows WHERE addr = '$addr_clear', keeping only the rows with the lowest trkg.curwipprice_online when matches produce multiple item names.

    Where does the WHERE in the OP fit into your #4 post?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    When I do that I loose the item_names on the unmatched plan rows.
    no. that's not possib;e

    if item_names is in the plan table, and the plan table is the left table in a LEFT OUTER JOIN, then you will always get it on all returned rows, matched and unmatched


    Quote Originally Posted by nichemtktg View Post
    Where does the WHERE in the OP fit into your #4 post?
    between the FROM clause and the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The trkg table also contain item names.

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There is no "between the FROM clause and the ORDER BY clause"

    So, I did this (because it made the most sense to me) , but it threw this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT OUTER JOIN ( SELECT cliorder , MIN(curwipprice_online) A' at line 6

    Here's the script:
    Code:
    SELECT *
      FROM plan 
      WHERE 	
    		(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
      
    LEFT OUTER 
      JOIN ( SELECT cliorder
                  , MIN(curwipprice_online) AS lowest
               FROM trkg
              WHERE addr = '$addr_clear'
    		  GROUP
                 BY cliorder ) AS x
        ON x.cliorder = plan.cliorder
    LEFT OUTER
      JOIN trkg 
        ON trkg.cliorder = x.cliorder
       AND trkg.curwipprice_online = x.lowest
       AND trkg.addr = '$addr_clear'
    Where does the WHERE go?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    The trkg table also contain item names.
    so, which column do you want to return?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nichemtktg View Post
    Where does the WHERE go?
    after the FROM clause, i.e. at the end

    then, if you decide you want to add an ORDER BY clause, it goes after the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your #12 post. I understand it.

    RE #11, The output needs to include all the cols from the qualified rows in the plan table:

    WHERE (TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end))

    with the curwipprice_online and addr cols appended at the end of the results when trkg rows qualify:

    (trkg.addr = '$addr_clear')

    The keys are important.

  14. #14
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    453
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I reversed the join, used your help, and a lot of trial and error. Voila!

    Thank-you very much r937.

    Code:
    SELECT 
    	plan.*, min(trkg.curwipprice_online) AS curwipprice_online, trkg.addr
    FROM 
    	trkg
    RIGHT OUTER JOIN 
    	plan
    ON 
    	plan.cliorder = trkg.cliorder
    AND 
    	trkg.addr = '$addr_clear'	
    WHERE 
    	TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) )
    GROUP BY 
    	plan.item_name;

  15. #15
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Data redundancy method can be used to avoid the duplicate rows and columns.In normalization the method will be used.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ionamartin123 View Post
    Data redundancy method can be used to avoid the duplicate rows and columns.
    oh, please, explain this "data redundancy method"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •