#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0

    How to do this (user var based) myql query in postgre ?


    Hi
    I'm trying to build a postgreql query that works in mysql (with user variables)
    Basically, I 'am starting from the following table (table0) ie a list of items ranked by id and perc :

    id item perc
    159 10 0.1751
    159 5 0.0987
    159 8 0.0924
    159 7 0.0874
    159 3 0.0672
    962 8 0.1542
    962 10 0.1375
    962 7 0.1222
    962 5 0.0920
    962 3 0.0673

    The query consists of selecting the first 2 items of each id and add a counter, ie the result must be :
    id item perc no
    159 10 0.1751 1
    159 5 0.0987 2
    962 8 0.1542 1
    962 10 0.1375 2
    etc...

    In mysql, the query that works uses user variables :
    SET @rownum := 0, @prev := 0;
    SELECT id, item, perc, no
    FROM (SELECT
    id,
    item,
    perc,
    IF( @prev <> id, @rownum := 1, @rownum := @rownum+1 ) AS no ,
    @prev := id FROM Table0 AS tmp
    ) as t5
    WHERE t5.no <= 2 ;

    Someone has an idea ? I need an equivalent postgre query , generic enough to work with aws redshift.
    Thanks for your help
    Patrick
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    The MySQL query is a hack around its lack of window functions.

    In any modern DBMS (including Postgres) this can be rewritten as:

    Code:
    select id, item, perc, no
    from (
      select id,
             item,
             perc,
             row_number() over (partition by id order by item desc) as no
    ) as t5
    WHERE t5.no <= 2 ;
    The next time please format your code and sample data using [code] tags (see my signature for additional information)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    I did not know it. Works fine and it's exactly what I was looking for.
    And since it is generic i 'll use it for other db as well.
    Thanks a lot.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Unfortunatey it does not work with AWS redshift as Row_number() is not supported (limited postresql support).
    Any trick in mind?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by pcanarelli
    Unfortunatey it does not work with AWS redshift as Row_number() is not supported (limited postresql support).
    Any trick in mind?
    The usual workaround is to use a count() statement, but that will most probably be quite slow.
    Something along the lines:
    Code:
    select id, 
           item, 
           perc
    from (
        select t1.id, 
               t1.item, 
               t1.perc,
               (select count(*) from the_table t2 where t2.id = t1.id and t2.item < t1.item) as no
        from the_table t1
    ) as t5
    WHERE t5.no <= 2;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Yes it may be.
    Thank you for your help

IMN logo majestic logo threadwatch logo seochat tools logo