MS SQL Server - Select Top of Each Category?

Author

Select Top of Each Category?

David_from_Chicago

2006-11-29, 7:12 pm

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
) ;[color=darkred]

Thanks in advance,

David_From_Chicago

Arnie Rowland

2006-11-29, 7:13 pm

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@14g2000cws.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
>

sajberek@gmail.com

2006-11-29, 7:13 pm

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)

David_from_Chicago

2006-11-29, 7:13 pm

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

David_from_Chicago

2006-11-29, 7:13 pm

I tried the Function method as well...
[color=darkred]
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:
[color=darkred]
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;

Arnie Rowland

2006-11-29, 7:13 pm

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@TK2MSFTNGP03.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@14g2000cws.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
>

Robert Klemme

2006-11-30, 7:14 pm

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

Sponsored Links