Sort by 2 columns

Oggle · #1 (**permalink**) 03-01-11, 02:55

I have a table of products that I want to sort by the following rules:

1.
Date in reverse.

2.
Products with similar names need to be next to each other.

Pattern is:

accessories-handbags-812zebrablack
accessories-handbags-812zebrabrown

We only match the text up to the last set of numbers. So in this case, the match is "accessories-handbags-812".

Anyone know how this can be accomplished by SQL?

aflorin27 · #2 (**permalink**) 03-01-11, 03:08

ORDER BY date DESC, name ASC

Oggle · #3 (**permalink**) 03-01-11, 03:28

Does that lead to the following sort?

Let's say there are 5 items:

A01black, 3/1/2010
A01white, 6/1/2010
A02yellow, 4/1/2010
A03blue, 10/1/2010
A03grey, 12/1/2010
A09green, 11/1/2010

After the sort they should appear like this:

A03grey, 12/1/2010
A03blue, 10/1/2010
A09green, 11/1/2010
A01white, 6/1/2010
A01black, 3/1/2010
A02yellow, 4/1/2010

Notice how A03* are piled together at the top, even though A03blue doesn't have the second latest date. And notice that A02yellow is below A01black, because the set A01* is higher since A01white has a date of 6/1/2010.

it-iss.com · #4 (**permalink**) 03-01-11, 04:28

So how do you determine the products? Will it always be a 3 alphanumeric code i.e. A01, A02, A03 with a color appended? If this is the code try:

Code:

select a.product, a.rel 
from {yourtable} a, 
        (select substr(product, 1, 3) as product, max(rel) as rel from {yourtable} group by substr(product, 1, 3)) b 
where substr(a.product, 1, 3) = b.product 
order by b.rel desc, a.rel desc;

+-----------+------------+
| product   | rel        |
+-----------+------------+
| A03grey   | 2010-01-12 | 
| A03blue   | 2010-01-10 | 
| A09green  | 2010-01-11 | 
| A01white  | 2010-01-06 | 
| A01black  | 2010-01-03 | 
| A02yellow | 2010-01-04 | 
+-----------+------------+
6 rows in set (0.00 sec)

Oggle · #5 (**permalink**) 03-01-11, 04:54

Thanks for the help. Actually the product name that needs to match varies by length. The thing that separates it is that there will always be a number followed by a word, like these:

clothing-dress-y-5255red
clothing-dress-y-5255redblack
clothing-dress-y-5255teal

The number varies by length, but always seem to be at least 3 digits.

r937 · #6 (**permalink**) 03-01-11, 05:13

Quote:

Originally Posted by Oggle

Does that lead to the following sort?

yes it does

Quote:

Originally Posted by Oggle

Notice how A03* are piled together at the top, even though A03blue doesn't have the second latest date. And notice that A02yellow is below A01black, because the set A01* is higher since A01white has a date of 6/1/2010.

so what? the rows ~are~ in the requred sort order -- date DESC, name ASC

it-iss.com · #7 (**permalink**) 03-01-11, 05:15

Can you give me an algorithm with which to determine the product names. For example it will always be up to and including a number i.e. A01, clothing-dress-y-5255 are products. However, there cannot be a product called clothing4men01 or is this allowed too?

Oggle · #8 (**permalink**) 03-01-11, 05:24

Quote:

Originally Posted by it-iss.com

Can you give me an algorithm with which to determine the product names. For example it will always be up to and including a number i.e. A01, clothing-dress-y-5255 are products. However, there cannot be a product called clothing4men01 or is this allowed too?

Here are a few. It's always in this format:
clothing-sweater-w5-97104charcoal (charcoal would be the difference)
clothing-sweater-gg2-p8120navy (navy would be the difference)
clothing-skirt-r2-91369fuchsiafloral (fuchsiafloral would be the difference)
swimsuit-onepeice-x5-3023tealsnk (tealsnk would be the difference)
swimsuit-onepiece-x5-9016honey (honey would be the difference)
clothing-outfit-ll9-j295red (red would be the difference)

So I would say at least 3 characters in front, followed by a number at least 2 digits long, followed by a word with no numbers in it.

healdem · #9 (**permalink**) 03-01-11, 05:46

so have you tired whats been suggested
and if so how does that outcome differ from what you have requested here, or against the original business requirement.
IE what is wrong with the current proposed solution from Aflorin

incidentally I thinbk it woudl make far more sense for you to try the proposed solution onyour own data rather than ask

Quote:

Does that lead to the following sort?

after all its your problem..
its your data...
its your business requirement...

as you have the data its quicker for you to test than expect soemone else to set data
as you know what is the expected outcome in full you can test that agaisnt your busienss requirment

Oggle · #10 (**permalink**) 03-01-11, 06:12

I ran the query it-iss.com suggested, but it doesn't seem to work. Here's a snippet of what I got:

Code:

clothing-dress-aa-d-11063black	20100430
clothing-dress-aa-d-11063blue	20100430
clothing-dress-aa-d-11063fuchsia	20100701
clothing-dress-aa-d-11063silver	20091217
clothing-dress-aa-d-11063ublack	20100708
clothing-dress-aa-d-11063ufuchsia	20100708
clothing-dress-aa-d-11063upurple	20100709
clothing-dress-aa-d-11063uwhite	20100708
clothing-dress-aa-d-11063white	20100430

Note that the dates should be in reverse order, with item of date "20100709" listed first in the group.

Also, here's another portion:

Code:

clothing-dress-aa-d-11066blackwhite	20091204
clothing-dress-aa-d-11067black	20100112
clothing-dress-aa-d-11067fuchsia	20100112
clothing-dress-aa-d-11067white	20100112
clothing-dress-aa-d-11068gold	20091210
clothing-dress-aa-d-11068grey	20091210
clothing-dress-aa-d-11068white	20091210
clothing-dress-aa-d-11078blackwhite	20091203
clothing-dress-aa-d-11079-sblack	20100310

The order should be like this instead:

Code:

clothing-dress-aa-d-11079-sblack	20100310
clothing-dress-aa-d-11067black	20100112
clothing-dress-aa-d-11067fuchsia	20100112
clothing-dress-aa-d-11067white	20100112
clothing-dress-aa-d-11068gold	20091210
clothing-dress-aa-d-11068grey	20091210
clothing-dress-aa-d-11068white	20091210
clothing-dress-aa-d-11066blackwhite	20091204
clothing-dress-aa-d-11078blackwhite	20091203

Oggle · #11 (**permalink**) 03-01-11, 06:15

Not sure if this matters, but the "date" field is actually a text string.

it-iss.com · #12 (**permalink**) 03-01-11, 06:39

I have written a MySQL stored function which will return the product model excluding the styling as follows:

Code:

--
--
-- Ronan Cashell (c) IT Integrated Business Solutions 2011
-- 01 March 2011
-- http://www.it-iss.com
--
DROP FUNCTION IF EXISTS getProduct;

DELIMITER $$
CREATE FUNCTION getProduct(aProduct VARCHAR(30))
RETURNS VARCHAR(30)
NO SQL
BEGIN
        DECLARE count INT;
        DECLARE i INT;
        DECLARE prod VARCHAR(30);
        SET prod = aProduct;
        SET count = LENGTH(aProduct);
        SET i = 0;
        WHILE i < count AND (SUBSTRING(prod, count-i, 1) < '0' OR SUBSTRING(prod, count-i, 1) > '9') DO
                SET i = i + 1;
        END WHILE;
        RETURN LEFT(prod, count-i);
END;
$$

DELIMITER ;

You should use this as follows in your query (this did not work in MySQL 5.0 but did in 5.5, I do not test in 5.1 so if you can confirm this):

Code:

SELECT a.product, a.rel  
FROM {yourtable} a,
          (SELECT getProduct(product) as product, max(rel) as rel
           FROM {yourtable} 
           GROUP BY getProduct(product)) b 
WHERE getProduct(a.product) = b.product 
ORDER BY b.rel desc, a.rel desc;

Oggle · #13 (**permalink**) 03-01-11, 07:01

Wow thanks for that! However I run MySQL 5. Any ideas on how to get it to work on 5.0?

healdem · #14 (**permalink**) 03-01-11, 07:06

providing
all the dates are written in year month day format
and
days and months are padded with leading 0's then it shouldn't matter (eg 1st March 2010 is stored as 20100301 as opposed to 201031.. its not clever (dates should be stored as date values), but it shouldn't matter

but what happens when you run the query with the sort order as suggested by aflorin?.

looking at the example you give in post #6 I don't think your propsoed order matches what you have said so far. as I read it the first item in the list should be
clothing-dress-aa-d-11067fuchsia 20100112 as its 12th Jan 2010

Oggle · #15 (**permalink**) 03-01-11, 07:35

To make this a bit more complicated, I'd like to then do this after the sorting is done:

Modify the "sort ID" field so that it increments each item exactly according to their position after the sort.

Is it possible to add this to the sort query as given by others who have replied previously?

Internet Services

Web Development

Digital Marketing

Consumer Tech