Selecting the 5 lowest from every group

Hello All-

I have a table containing items which each have an associated group
(and a price). I have been trying to figure out how, in one statement,
to select the 5 lowest priced items in every group. I could use the MIN
function with a GROUP BY to find the lowest, but how could I find the N
lowest? Thanks very much in advance...

-- whit

pengypenguin@gmail.com wrote:
> Hello All-
>
> I have a table containing items which each have an associated group
> (and a price). I have been trying to figure out how, in one statement,
> to select the 5 lowest priced items in every group. I could use the MIN
> function with a GROUP BY to find the lowest, but how could I find the N
> lowest? Thanks very much in advance...
>
> -- whit

What happens in the event of item 5 and item 6 having the same price?

pengypenguin@gmail.com wrote:
> Hello All-
>
> I have a table containing items which each have an associated group
> (and a price). I have been trying to figure out how, in one statement,
> to select the 5 lowest priced items in every group. I could use the MIN
> function with a GROUP BY to find the lowest, but how could I find the N
> lowest? Thanks very much in advance...
>
> -- whit

Anyway, it's something like this. This probably isn't quite right
because it's kind of off the top of my head, untested and I don't know
what your tables look like but you should be able to get the idea.

SELECT item, price from (
select i.item, i.price, rank
from items i
join
(select count(*) rank,
a.item,
from items A join
items B
on (A.item = B.item
and A.price = B.price
)
or (A.item = B.item
and A.price > B.price)
group by A.item) N
on i.item= N.item
order by i.item, rank) xxx
where rank <= 5
group by item
order by price, item;

If it's not right the following link might help. If necessary, see
bugmenot.com for login info.
http://searchoracle.techtarget.com/a...01455,00l.html

It's alright to break a tie randomly.

strawberry wrote:
> pengypenguin@gmail.com wrote:
> > Hello All-
> >
> > I have a table containing items which each have an associated group
> > (and a price). I have been trying to figure out how, in one statement,
> > to select the 5 lowest priced items in every group. I could use the MIN
> > function with a GROUP BY to find the lowest, but how could I find the N
> > lowest? Thanks very much in advance...
> >
> > -- whit
>
> What happens in the event of item 5 and item 6 having the same price?

So it would seem I have to run MIN multiple times, each request asking
only for values lower than the previous MIN. I wish there were a more
elegant method...

Thanks for your help. :)

-- whit

strawberry wrote:
> pengypenguin@gmail.com wrote:
> > Hello All-
> >
> > I have a table containing items which each have an associated group
> > (and a price). I have been trying to figure out how, in one statement,
> > to select the 5 lowest priced items in every group. I could use the MIN
> > function with a GROUP BY to find the lowest, but how could I find the N
> > lowest? Thanks very much in advance...
> >
> > -- whit
>
> Anyway, it's something like this. This probably isn't quite right
> because it's kind of off the top of my head, untested and I don't know
> what your tables look like but you should be able to get the idea.
>
> SELECT item, price from (
> select i.item, i.price, rank
> from items i
> join
> (select count(*) rank,
> a.item,
> from items A join
> items B
> on (A.item = B.item
> and A.price = B.price
> )
> or (A.item = B.item
> and A.price > B.price)
> group by A.item) N
> on i.item= N.item
> order by i.item, rank) xxx
> where rank <= 5
> group by item
> order by price, item;
>
> If it's not right the following link might help. If necessary, see
> bugmenot.com for login info.
> http://searchoracle.techtarget.com/a...01455,00l.html

pengypenguin@gmail.com wrote:
> So it would seem I have to run MIN multiple times, each request asking
> only for values lower than the previous MIN. I wish there were a more
> elegant method...
>
> Thanks for your help. :)
>
> -- whit
>
> strawberry wrote:
> > pengypenguin@gmail.com wrote:
> > > Hello All-
> > >
> > > I have a table containing items which each have an associated group
> > > (and a price). I have been trying to figure out how, in one statement,
> > > to select the 5 lowest priced items in every group. I could use the MIN
> > > function with a GROUP BY to find the lowest, but how could I find the N
> > > lowest? Thanks very much in advance...
> > >
> > > -- whit
> >
> > Anyway, it's something like this. This probably isn't quite right
> > because it's kind of off the top of my head, untested and I don't know
> > what your tables look like but you should be able to get the idea.
> >
> > SELECT item, price from (
> > select i.item, i.price, rank
> > from items i
> > join
> > (select count(*) rank,
> > a.item,
> > from items A join
> > items B
> > on (A.item = B.item
> > and A.price = B.price
> > )
> > or (A.item = B.item
> > and A.price > B.price)
> > group by A.item) N
> > on i.item= N.item
> > order by i.item, rank) xxx
> > where rank <= 5
> > group by item
> > order by price, item;
> >
> > If it's not right the following link might help. If necessary, see
> > bugmenot.com for login info.
> > http://searchoracle.techtarget.com/a...01455,00l.html

Try to avoid top posting (writing your reply above the previous
response). My reader handles it well but some people find the habot a
teeny bit irritating. You have been warned ;-)

Ignore my solution. I was trying to think of a way to deal with ties
and lost the plot a little.

This solution adapted from one by Bill Karwin should just about work
(although ties still aren't properly resolved):

select t1.item_id, t1.item, t1.price
from items t1
left join items t2 on t1.item = t2.item and t1.item_id <>
t2.item_id and t1.price >= t2.price
group by t1.item, t1.item_id
having count(*) < 3
order by t1.item,t1.price,t1.item_id