How to get specific number of rows from each group

I need a SQL query to get 2 items from each catergory.
And if possible, 2 items with price < $100 and 1 item with price >= $100
from each caterogy.
Table: tblItems
Fields: ItemID, CategoryID, ItemName, ItemPrice
A stored procedure with multiple queries also works.
Thanks,
--
GeeviSelect * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price
Union
Select Top 1 PK From Table
Where Price >- 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Sorry, Union won't work because you can;t use Order by in parts of a Union..
.
So you have to use separate In Predicate conditions...
Select * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||aaaghhh!! Forgot to alias the first Table...
Select * From Table As T -- Left off the "As T" Before
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Burying the TOP .. ORDER BY should work:
Select * From Table as T
Where PK In (
select PK from (
Select Top 2 PK From Tbl
Where Price < 100
And Category = T.Category
Order By Price
) PartA
union
select PK from (
Select Top 1 PK From Tbl
Where Price >= 100
And Category = T.Category
Order By Price Desc
) PartB
)
-- I don't know how to get 2 items, of which 2 have prices < $100
-- and one has price >= $100.
Steve Kass
Drew University
CBretana wrote:
> Sorry, Union won't work because you can;t use Order by in parts of a Union
..
> So you have to use separate In Predicate conditions...
>
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price)
> Or PK In
> (Select Top 1 PK From Table
> Where Price >= 100
> And Category = T.Category
> Order By Price Desc)
>
> "Geevi" wrote:
>|||Great! It works!
I could not use the Order By "Price" as "ORDER BY items must appear in the
select list if the statement contains a UNION operator."
But this is a big time saver for me.
Thanks CBretana, for the solution!
"CBretana" wrote:
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price
> Union
> Select Top 1 PK From Table
> Where Price >- 100
> And Category = T.Category
> Order By Price Desc)
>
>
>
> "Geevi" wrote:
>