Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: First n records of each group

    DB2 z/OS V8

    CAR tbl ( 1 row by each car sold)
    Cols => SYEAR : Sale Year
    CYEAR : Car Year
    MAKE : Car Manufacturer
    MODEL: Car Model

    Request: Select the ten most sold cars per year (between 2 given years)
    SALE YEAR CYEAR-MAKE-MODEL COUNT
    ---------- -------------------- --------
    2000 X10 CX10
    X09 CX09
    ... ...
    2001 Y10 CY10
    Y09 CY09
    ... ...
    2002 Z10 CZ10
    Z09 CZ09
    ... ...

    Thanks for your help, ARLF.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like homework

    can we see your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Use the ROW_NUMBER function with the window-partitioning clause to number the rows in each group, then select all rows with a row number in the respective range.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2006
    Posts
    16
    Thanks Knut,
    initially, the report was made with 2 programs using cursors, work files and sort (mainframe). However, I was looking for a solution that could be run under QMF.
    Something like
    SELECT syear, cyear, make, model, c1,
    DENSE_RANK() OVER(PARTITION BY syear ORDER BY c1 DESC) AS r1
    FROM ( SELECT syear, cyear, make, model, count(*) as c1
    FROM car
    WHERE syear BETWEEN 2000 AND 2007
    GROUP BY syear, cyear, make, model ) AS T1
    WHERE r1 < 11
    ORDER BY syear, c1 desc;

    Saludos, ARLF.

Posting Permissions

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