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:
>

How to get rid of the zeros in money type, Thanks!

How to get rid of the zeros in money type,

i.e. only show dollars, no cents?

I use:

CONVERT(varchar, Price, 1) AS Price

It gives me the result with 2 digits decimals. like 123,456.00

I only need the dollar part, how to get rid of the zeros, Thanks!

CONVERT(int, a) AS Price

CONVERT(numeric, a) AS Price

Also take a look at the following for CAST and CONVERT

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

|||

Consider the functions CEILING() and FLOOR().

Zlatko