Search Postgresql Archives

Re: Select first ten of each category?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 4/13/06, Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx> wrote:
I'm stumped on this one...

I have a table defined thusly:

create table items (
id serial,
category integer not null references category(id),
name varchar not null,
price real,
unique(category, name));

It has a LARGE number of entries. I'd like to grab the 10 most expensive items
from each category in a single query. How can this be done? Something like

Select items.*
FROM items
where id IN (
        select firstTen(id) FROM items
        group by category
        ORDER BY price DESC
        )
ORDER BY price desc;

But I've not found any incantation to make this idea work...

I came up with something like this:

SELECT i.* FROM items i JOIN (
    SELECT category, (
        SELECT price FROM items ii
            WHERE ii.category=io.category
            ORDER BY price DESC OFFSET 9 LIMIT 1
        ) AS date FROM items io GROUP BY category) AS sel
    ON (i.category=sel.category AND i.price >= sel.price);

I,e.  First do a select which will return ninth price of each
category (two inner selects) and then JOIN it with a whole
table, where category and price match they way you want.

Keep in mind, if you happen to have same prices at position
near 10th, you'll end up having more than 10 returns per
given category.  Either filter it at application level, or embed
some additional key inside the join condition (like
ON (i.category=sel.category AND (i.price > sel.price OR
(i.price=sel.price AND i.id=sel.last_id)));

  Regards,
        Dawid



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux