# Thread: Sort by 2 columns

1. Registered User
Join Date
Mar 2011
Posts
12

## Unanswered: Sort by 2 columns

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?

2. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
577
Provided Answers: 3
ORDER BY date DESC, name ASC

3. Registered User
Join Date
Mar 2011
Posts
12
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.

4. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
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)```
Last edited by it-iss.com; 03-01-11 at 04:46. Reason: Correction on order by clause should have added a.rel desc too.

5. Registered User
Join Date
Mar 2011
Posts
12
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.

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Originally Posted by Oggle
Does that lead to the following sort?
yes it does

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

7. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
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?

8. Registered User
Join Date
Mar 2011
Posts
12
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.

9. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
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
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

10. Registered User
Join Date
Mar 2011
Posts
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```

11. Registered User
Join Date
Mar 2011
Posts
12
Not sure if this matters, but the "date" field is actually a text string.

12. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
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;```

13. Registered User
Join Date
Mar 2011
Posts
12
Wow thanks for that! However I run MySQL 5. Any ideas on how to get it to work on 5.0?

14. Jaded Developer
Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Provided Answers: 59
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

15. Registered User
Join Date
Mar 2011
Posts
12
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?

#### Posting Permissions

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