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

pepsi_max2k · #1 (**permalink**) 04-16-10, 07:11

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

dav1mo · #2 (**permalink**) 04-16-10, 08:13

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

pepsi_max2k · #3 (**permalink**) 04-16-10, 08:21

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...

it-iss.com · #4 (**permalink**) 04-16-10, 08:54

Try

SELECT MAX(stockid) AS stockid, productid, new, sealed
FROM stock
GROUP BY productid, new, sealed
ORDER BY 1;

pepsi_max2k · #5 (**permalink**) 04-16-10, 09:09

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.

it-iss.com · #6 (**permalink**) 04-16-10, 10:12

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?

pepsi_max2k · #7 (**permalink**) 04-16-10, 10:49

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.

it-iss.com · #8 (**permalink**) 04-16-10, 11:00

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);

pepsi_max2k · #9 (**permalink**) 04-16-10, 11:56

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?

it-iss.com · #10 (**permalink**) 04-16-10, 12:14

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

Internet Services

Web Development

Digital Marketing

Consumer Tech