Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    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. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    ORDER BY date DESC, name ASC
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    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. #4
    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 05:46. Reason: Correction on order by clause should have added a.rel desc too.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Oggle View Post
    Does that lead to the following sort?
    yes it does

    Quote Originally Posted by Oggle View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by it-iss.com View Post
    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. #9
    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
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    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. #11
    Join Date
    Mar 2011
    Posts
    12
    Not sure if this matters, but the "date" field is actually a text string.

  12. #12
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  13. #13
    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. #14
    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
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    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
  •