Results 1 to 6 of 6

Thread: Wisegroup

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Wisegroup

    Hello all,

    I want to select complete row from a table:


    +-------+------------+------------+
    | group | date | price |
    +-------+------------+------------+
    | 1 | 2009-03-01 | 300.00 |
    | 1 | 2009-03-01 | 299.00 |
    | 2 | 2009-03-02 | 302.00 |
    | 2 | 2009-03-02 | 298.00 |
    | 2 | 2009-03-14 | 300.00 |
    +-------+------------+------------+


    FOR EACH group SELECT * FROM table ORDER BY date, price LIMIT 1;

    The problem is because I need sorting on two columns.

    I reviewed "groupwise" recipes, but there are simpler examples.



    Cheers
    Artur

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Some (untested) alternatives:

    SELECT "group", "date", MAX(price)
    FROM thistable
    WHERE ("group", "date") IN (SELECT "group", MAX("date") FROM thistable GROUP BY "group")
    GROUP BY "group", "date"


    The following feature outside Core SQL-99 is used:
    F641, "Row and table constructors"



    SELECT "group", "date", MAX(price)
    FROM thistable AS outmst
    WHERE "date" = (SELECT MAX("date") FROM thistable WHERE "group" = outmst."group")
    GROUP BY "group", "date"


    Core SQL-99.


    Note that both GROUP and DATE are reserved words in ANSI/ISO SQL. It's a good idea to double quote those column names to avoid future problems.

  3. #3
    Join Date
    Mar 2009
    Posts
    3

    Performance

    Many thanks. Results are as I wanted.
    Probably I need to add some idexes. Table is so big, that on 2GHz didn't finish in 10 minutes.


    Thanks
    Artur

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Careful with the quoting. As written by JarlH, you must have created the table using lower-case identifiers. The quoting turns off the implicit conversion to upper case.

    Yet another approach is this:
    Code:
    SELECT group, date,
           ( SELECT MAX(price)
             FROM thistable AS i
             WHERE  i.date = o.date AND
                    i.group = o.group ) AS max_price
    FROM   thistable
    GROUP BY group, date
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2009
    Posts
    3

    Column names

    I;m sorry - I made a mess using such names in the example. In my database the names are different (pure Polish) according to company policy.
    .
    Thanks for the example, I'll check it tomorrow.
    .
    Today I added new missing index, but I didn't gain gratifying performance. Still working on it.



    Thanks
    Artur

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What was the index you added? And what SQL are you using?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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