Specify number of row for each group by

[don2241]

Hi
Is it possible to have a sql that gets x amount of rows for each group by

For example:
Get the 3 latest prices that we charged customers for all items sold:
Customer Item Price
abc 123 5.00
abc 123 6.00
abc 123 7.00
cba 321 5.00
cba 321 7.00
cba 321 8.00
xxx 123 10.00
xxx 123 11.00
xxx 123 12.00

I have a date to for each sale that I must use to establish what is the latest but how can I limit to show only 3 for each customer-item

Thank you for any response

 

[blom0344]

Depending on your platform you could write a statement with an inline view using rank()

select temp.xcust,
temp.xitem,
temp.xprice from
(select customer as xcust,
item as xitem,
price as xprice,
rank() over (partition by customer order by date desc) as
xrank
order by date desc) temp
where temp.xrank <4

Ties Blom

 

[MarcLodge]

Also depending on your platform, you could try a subselect to the same table using the FETCH FIRST 3 ROWS clause. I seem to recall though that this has problems on some platforms and not on others - I can't get it to work on my zOS mainframe. See thread178-1173569 for info.

Marc

 

[blom0344]

FETCH FIRST 3 ROWS

For multiple customers?

Ties Blom

 

[MarcLodge]

I was thinking of having an inner subselect with the fetch first 3 rows clause that would like to the external select on unique values, in this instance the customer and item.

As I mentioned, I haven't been able to test this out, so it's just theory on my part. Quite possible I've misunderstood the requirement too

 

[blom0344]

Graeme Birchall offers some alternatives to OLAP functions in the SQL cookbooks. Not really easy to understand or write and very costly, but in theory there should be solution possible with OLAP style.
Perhaps you mean the solution he proposes?

Ties Blom

 

[don2241]

The request for the sql has been terminated but
thank you for the help and responses.

Have fun

 

[blom0344]

Just out of curiosity, would it have met the requirement?

Ties Blom