Select Top of Each Category?

I'm trying to get the TOP 30 prices for each ItemId (first 30 prices by
date) using SQL Server 2000. I have read that I may need to use a
function, but I was hoping there might be an easier way.  If as User
Defined Function is needed I could certainly use some guidance on how
to call it correctly.

I think the challenge I'm facing is that I need the subquery (WHERE
criteria) to join on 2 fields, PriceDate and ItemId.

PriceTable has fields PriceDate, ItemId, Price

I was hoping something like the following might work...(but it pulls
all records for Item  '00001')[color=darkr
ed] 
SELECT
*
FROM PriceTable as A
WHERE EXISTS (
SELECT TOP 30
*
FROM PriceTable B
WHERE
A.ItemId = B.HedgeId
AND A.PriceDate = B.PriceDate
AND ItemId = '00001'  /* testing for one item first.. this
line will be removed */
ORDER BY B.PriceDate ASC
) ; 

Thanks in advance,

David_From_Chicago

You idea is very close, perhaps something like this will work for you:

SELECT
A.ItemID,
A.HedgeId,
B.PriceDate
FROM PriceTable A
JOIN ( SELECT TOP 30 *
FROM PriceTable
WHERE (   ItemId    = A.HedgeId
AND PriceDate = A.PriceDate
)
) B
ON A.ItemID = B.ItemID
WHERE A.ItemId = '00001' 
ORDER BY 
A.ItemID,
B.PriceDate;

-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


"David_from_Chicago" <dpfollmer@yahoo.com> wrote in message news:1164830483.079845.319100@1
4g2000cws.googlegroups.com...
> I'm trying to get the TOP 30 prices for each ItemId (first 30 prices by
> date) using SQL Server 2000. I have read that I may need to use a
> function, but I was hoping there might be an easier way.  If as User
> Defined Function is needed I could certainly use some guidance on how
> to call it correctly.
> 
> I think the challenge I'm facing is that I need the subquery (WHERE
> criteria) to join on 2 fields, PriceDate and ItemId.
> 
> PriceTable has fields PriceDate, ItemId, Price
> 
> I was hoping something like the following might work...(but it pulls
> all records for Item '00001') 
> SELECT
>    *
> FROM PriceTable as A
> WHERE EXISTS (
>    SELECT TOP 30
>          *
>     FROM PriceTable B
>     WHERE
>          A.ItemId = B.HedgeId
>          AND A.PriceDate = B.PriceDate
>          AND ItemId = '00001'  /* testing for one item first.. this
> line will be removed */
>     ORDER BY B.PriceDate ASC
>     ) ; 
> 
> Thanks in advance,
> 
> David_From_Chicago
>

Arnie I don't think that your solution would even compile on SQL
Server.

Try something like this:

SELECT
*
FROM
PRICETABLE T1
WHERE
T1.ITEMID IN (SELECT TOP 2 ITEMID FROM PRICETABLE T2 WHERE T1.HEDGEID
= T2.HEDGEID ORDER BY ITEMID)

The problem with this query is that Table Alias "A" is not visible in
the subquery.  This is why I tried to use the WHERE criteria instead.

The solution sajbe...@gmail.com sent also won't work because it will
only select the top 2 items.  I want the top 2 dates (or prices) from
each item/hedge.

Final result should look like:
Item Date
1  1/2/2006
1  1/3/2006
2  3/3/2003
2  3/4/2003
etc

Arnie Rowland  wrote:
> You idea is very close, perhaps something like this will work for you:
>
> SELECT
>    A.ItemID,
>    A.HedgeId,
>    B.PriceDate
> FROM PriceTable A
>    JOIN ( SELECT TOP 30 *
>           FROM PriceTable
>           WHERE (   ItemId    = A.HedgeId
>                 AND PriceDate = A.PriceDate
>                 )
>         ) B
>       ON A.ItemID = B.ItemID
> WHERE A.ItemId = '00001'
> ORDER BY
>    A.ItemID,
>    B.PriceDate;
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc

I tried the Function method as well...
 
SELECT
*
,A.PriceDate AS tryme
FROM PriceTable as A
INNER JOIN dbo. udf_GETTop30DailyPri
ces('00001') AS B
ON A.ItemId= B.ItemId
AND A.PriceDate = B.PriceDate
ORDER BY 1, 2 DESC;

The above code works great (for Item '00001') and returns a correct 30
records.  But, when I try to feed it the ItemId dynamically I get the
following error:

"...is not a recognized OPTIMIZER LOCK HINTS option"

See "dynamic" code below:
 
SELECT
*
,A.PriceDate AS tryme
FROM PriceTable as A
INNER JOIN dbo. udf_GETTop30DailyPri
ces(tryme) AS B
ON A.ItemId= B.ItemId
AND A.PriceDate = B.PriceDate
ORDER BY 1, 2 DESC;

It was on the top, untested. 

Sometimes things are easier when table DDL and sample data are included.

-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


"Arnie Rowland" <arnie@1568.com> wrote in message news:%23Mn0ob$EHHA.5004@TK
2MSFTNGP03.phx.gbl...
You idea is very close, perhaps something like this will work for you:

SELECT
A.ItemID,
A.HedgeId,
B.PriceDate
FROM PriceTable A
JOIN ( SELECT TOP 30 *
FROM PriceTable
WHERE (   ItemId    = A.HedgeId
AND PriceDate = A.PriceDate
)
) B
ON A.ItemID = B.ItemID
WHERE A.ItemId = '00001' 
ORDER BY 
A.ItemID,
B.PriceDate;

-- 
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience. 
Most experience comes from bad judgment. 
- Anonymous

You can't help someone get up a hill without getting a little closer to the 
top yourself.
- H. Norman Schwarzkopf


"David_from_Chicago" <dpfollmer@yahoo.com> wrote in message news:1164830483.079845.319100@1
4g2000cws.googlegroups.com...
> I'm trying to get the TOP 30 prices for each ItemId (first 30 prices by
> date) using SQL Server 2000. I have read that I may need to use a
> function, but I was hoping there might be an easier way.  If as User
> Defined Function is needed I could certainly use some guidance on how
> to call it correctly.
> 
> I think the challenge I'm facing is that I need the subquery (WHERE
> criteria) to join on 2 fields, PriceDate and ItemId.
> 
> PriceTable has fields PriceDate, ItemId, Price
> 
> I was hoping something like the following might work...(but it pulls
> all records for Item '00001') 
> SELECT
>    *
> FROM PriceTable as A
> WHERE EXISTS (
>    SELECT TOP 30
>          *
>     FROM PriceTable B
>     WHERE
>          A.ItemId = B.HedgeId
>          AND A.PriceDate = B.PriceDate
>          AND ItemId = '00001'  /* testing for one item first.. this
> line will be removed */
>     ORDER BY B.PriceDate ASC
>     ) ; 
> 
> Thanks in advance,
> 
> David_From_Chicago
>

On 29.11.2006 21:01, David_from_Chicago  wrote:
> I'm trying to get the TOP 30 prices for each ItemId (first 30 prices by
> date) using SQL Server 2000. I have read that I may need to use a
> function, but I was hoping there might be an easier way.  If as User
> Defined Function is needed I could certainly use some guidance on how
> to call it correctly.
>
> I think the challenge I'm facing is that I need the subquery (WHERE
> criteria) to join on 2 fields, PriceDate and ItemId.
>
> PriceTable has fields PriceDate, ItemId, Price
>
> I was hoping something like the following might work...(but it pulls
> all records for Item '00001')
> SELECT
>     *
> FROM PriceTable as A
> WHERE EXISTS (
>     SELECT TOP 30
>           *
>      FROM PriceTable B
>      WHERE
>           A.ItemId = B.HedgeId
>           AND A.PriceDate = B.PriceDate
>           AND ItemId = '00001'  /* testing for one item first.. this
> line will be removed */
>      ORDER BY B.PriceDate ASC
>      ) ;
>
> Thanks in advance,

Here's a solution: the first two queries are SQL 2005, the last one is
2K.  You just need to adjust column and table names and the selection
criterion ("<= 30").

Kind regards

robert


begin transaction

create table #ranking_test (
fake_date int,
product_id int,
insert_sequence int identity(1,1)
)

insert into #ranking_test (fake_date, product_id) values (1,1)
insert into #ranking_test (fake_date, product_id) values (2,1)
insert into #ranking_test (fake_date, product_id) values (3,1)
insert into #ranking_test (fake_date, product_id) values (4,1)
insert into #ranking_test (fake_date, product_id) values (5,1)
insert into #ranking_test (fake_date, product_id) values (6,1)

insert into #ranking_test (fake_date, product_id) values (10,2)
insert into #ranking_test (fake_date, product_id) values (20,2)
insert into #ranking_test (fake_date, product_id) values (30,2)
insert into #ranking_test (fake_date, product_id) values (40,2)
insert into #ranking_test (fake_date, product_id) values (50,2)
insert into #ranking_test (fake_date, product_id) values (60,2)

insert into #ranking_test (fake_date, product_id) values (11,3)
insert into #ranking_test (fake_date, product_id) values (23,3)
insert into #ranking_test (fake_date, product_id) values (2,3)
insert into #ranking_test (fake_date, product_id) values (45,3)
insert into #ranking_test (fake_date, product_id) values (1,3)
insert into #ranking_test (fake_date, product_id) values (64,3)

/*
-- watch the inner query:
select product_id
, fake_date
, insert_sequence
, row_number() over ( partition by product_id order by fake_date ) "row
number"
from #ranking_test
*/

-- show only the first three of each
select product_id
, fake_date
, insert_sequence "seq"
from (
select product_id
, fake_date
, insert_sequence
, row_number() over ( partition by product_id order by fake_date ) "row
number"
from #ranking_test
) rankings
where "row number" < 4
order by product_id, fake_date

-- SQL 2000 code
select rt1.product_id, rt1.fake_date, count(*) "row number"
from #ranking_test rt1, #ranking_test rt2
where rt2.product_id = rt1.product_id
and rt2.fake_date <= rt1.fake_date
group by rt1.product_id, rt1.fake_date
having count(*) < 4
order by rt1.product_id, count(*), rt1.fake_date

rollback