Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2010
    Posts
    9

    Unanswered: How to pick out unique rows from query based on multiple columns.

    Hey all, I have a question that's hard to describe without showing the table, so...

    Code:
    stockid		productid	new		sealed
    1		101		0		0
    2		101		0		0
    3		101		1		0
    4		101		1		1
    5		101		1		0
    I need to output the results to a php index page, with only a single entry for every used (!new && !sealed), new (new && !sealed) and sealed (new && sealed) item, and this should be the one with the highest stockid. ie:

    Code:
    stockid		productid	new		sealed
    2		101		0		0
    4		101		1		1
    5		101		1		0
    And I've noooo idea how it should, or could, be done. MySQL? More select statements, or match? PHP? Stick things in an arry? Or just loop through all entries?

    Any advice apreciated

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about selecting the stockid,productid, max(case !new && !sealed) as used, max(case new && !sealed) as new_not_sealed, max(case new && sealed) as new. don't forget your grouping and rest of SQL statement.
    Dave

  3. #3
    Join Date
    Apr 2010
    Posts
    9
    Just tried this:

    Code:
    SELECT * FROM stock GROUP BY productid, new, sealed
    and it almost works, except only picks up the lowest (not highest) stock id as-is.

    I'll try yours, dave. Is that actual MySQL code there? I was just using ! and && as examples, had no idea it would actually be recognized as code (at least in sql)...


    Hey hey:


    Code:
    SELECT * FROM 
    
    (SELECT * FROM stock
    ORDER BY stockid DESC) AS stock
    
    GROUP BY productid, new, sealed
    SELECT * FROM stock
    ORDER BY stockid DESC

    Gives me all results but in reverse order. The GROUP BY then behaves as before, but the "first" entries it picks are actually the "last" entries as the table is in reverse order May well be a horrible work around to a simple problem though...
    Last edited by pepsi_max2k; 04-16-10 at 09:32.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try

    SELECT MAX(stockid) AS stockid, productid, new, sealed
    FROM stock
    GROUP BY productid, new, sealed
    ORDER BY 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2010
    Posts
    9
    Code:
    SELECT MAX(stockid) AS stockid, productid, new, sealed
    FROM stock
    GROUP BY productid, new, sealed
    ORDER BY 1;
    Doesn't seem to work as well. It seems to affect the stockid's, where it still picks up the earliest entry for each grouping, but outputs this with the latest entrie's stockid.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you provide an example of what you are looking for? I tested this code based on your example above and it returns the data as you requested, so there must be something I am just not following with your question?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Apr 2010
    Posts
    9
    The problem's only visible when you have more info in the stock table (which I do). Eg:

    Code:
    stockid		productid	new		sealed		price
    1		101		0		0		4
    2		101		0		0		7
    3		101		1		0		5
    4		101		1		1		9
    5		101		1		0		8

    Query + result. Only assumed based on this data, i've not tested with the above table exactly but it's what happened with my real one, at least it seems to be what happened, though I don't really know why, so it may be something else causing it. Anyway...:

    Code:
    SELECT * FROM stock
    GROUP BY productid, new, sealed
    
    
    stockid		productid	new		sealed		price
    1		101		0		0		4
    3		101		1		0		5
    4		101		1		1		9

    The above selects unique prod/new/sealed groups, but picks out only the first (lowest stockid) entry for each grouping.


    Code:
    SELECT MAX(stockid) AS stockid, productid, new, sealed
    FROM stock
    GROUP BY productid, new, sealed
    ORDER BY 1;
    
    
    stockid		productid	new		sealed		price
    2		101		0		0		4
    5		101		1		0		5
    4		101		1		1		9
    The above selects unique prod/new/sealed groups, and still picks out only the first (lowest stockid) entry for each grouping, so is essentially the same as the first select query. However it changes the stockid to that of the later (highest) stockid for each grouping. At least that's my experience.

    Code:
    SELECT * FROM 
    (SELECT * FROM stock
    ORDER BY stockid DESC) AS stock
    GROUP BY productid, new, sealed
    
    
    
    stockid		productid	new		sealed		price
    2		101		0		0		7
    4		101		1		1		9
    5		101		1		0		8
    And this one first sorts the table in descending order by stockid, and then runs the same as the first select query above, though as the table is in reverse order, the "first" entry for each productid/new/sealed grouping is now the highest stockid.
    Last edited by pepsi_max2k; 04-16-10 at 11:59.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This provides me with the missing piece of information. What you are looking for actually is to have the latest stockid (uniquely defined across productid, new and sealed) but then used to pick up all the remaining information:

    SELECT * FROM stock
    WHERE stockid IN
    (SELECT MAX(stockid)
    FROM stock
    GROUP BY productid, new, sealed);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Apr 2010
    Posts
    9
    Ok, to confuse you even more, here's my full query (I left quite a lot out originally).

    Code:
    SELECT * FROM (
    
    
        /* Select each stock entry, joining info from product tables. */
        SELECT 
        stockid, productid, price_over, postage_over, new, sealed, rerelease, condition_item, condition_man, condition_pack, comment, ean, ean2, eanalt, eanalt2, cat, name, console, genre, description, bullets, box, pack_price, pack_type, class, post_price, max 
        FROM stock 
        
        LEFT JOIN (
            SELECT * FROM store_products
            UNION
            SELECT * FROM products ) AS all_products
        ON stock.productid = all_products.id
        
        /* Join packaging info to stock. */
        LEFT JOIN ( 
            SELECT packaging.type AS pack_type, packaging.price AS pack_price 
            FROM packaging) AS packaging
        ON packaging = packaging.pack_type
        
        /* Join postage info to stock, choosing only 2nd class price. */
        LEFT JOIN (
            SELECT class, size, weight, postage.price AS post_price
            FROM postage) AS postage
        ON all_products.size = postage.size
        AND all_products.weight = postage.weight 
        AND (postage.class = 2 OR postage.class IS NULL)
        
        /* Join eBay prices to stock, chosing only latest price. */
        LEFT JOIN prices_ebay
        ON productid = prices_ebay.id
        WHERE (prices_ebay.date = (select max(prices_ebay.date) from prices_ebay  
        WHERE productid = prices_ebay.id) OR prices_ebay.date IS NULL)
        
        
        /* FIGURE OUT HOW TO SHOW ONLY MAX ITEM COND RATED USED ITEM */
        
        
    /* Put table in reverse order so group by only selects latest entries. */
    ORDER BY stockid DESC ) AS stock
    
    /* Select only one, latest, entry for each used / new /sealed product */
    GROUP BY productid, new, sealed
    
    ORDER BY name ASC, sealed DESC, new DESC

    That uses my order by method which works fine.

    The "WHERE stockid IN ..." bit works fine on a simple example, but I can't figure out where it would fit in in the above. If I could, would it be any more efficient than I already have? Or should I just stick with what I've got seeings how it's working fine?

  10. #10
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You would add it in towards the end as follows:

    Code:
    SELECT * FROM (
    
    
        /* Select each stock entry, joining info from product tables. */
        SELECT 
        stockid, productid, price_over, postage_over, new, sealed, rerelease, condition_item, condition_man, condition_pack, comment, ean, ean2, eanalt, eanalt2, cat, name, console, genre, description, bullets, box, pack_price, pack_type, class, post_price, max 
        FROM stock 
        
        LEFT JOIN (
            SELECT * FROM store_products
            UNION
            SELECT * FROM products ) AS all_products
        ON stock.productid = all_products.id
        
        /* Join packaging info to stock. */
        LEFT JOIN ( 
            SELECT packaging.type AS pack_type, packaging.price AS pack_price 
            FROM packaging) AS packaging
        ON packaging = packaging.pack_type
        
        /* Join postage info to stock, choosing only 2nd class price. */
        LEFT JOIN (
            SELECT class, size, weight, postage.price AS post_price
            FROM postage) AS postage
        ON all_products.size = postage.size
        AND all_products.weight = postage.weight 
        AND (postage.class = 2 OR postage.class IS NULL)
        
        /* Join eBay prices to stock, chosing only latest price. */
        LEFT JOIN prices_ebay
        ON productid = prices_ebay.id
        WHERE (prices_ebay.date = (select max(prices_ebay.date) from prices_ebay  
        WHERE productid = prices_ebay.id) OR prices_ebay.date IS NULL)
    
    WHERE stockid IN (SELECT MAX(stockid) FROM stock GROUP BY productid, new, sealed)
        
        
        /* FIGURE OUT HOW TO SHOW ONLY MAX ITEM COND RATED USED ITEM */
        
        
    /* Put table in reverse order so group by only selects latest entries. */
    ORDER BY stockid DESC ) AS stock
    
    ORDER BY name ASC, sealed DESC, new DESC
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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