Need to Select rows from a Top 10 Result
-
I need to select specific rows from the results of a TOP 10 selection. I found enough information to put together this query using a derived table but I am getting an invalid object error. It looks like it should work. Any suggestions would be greatly appreciated:
USE Northwind
GO
SELECT o.*, r.rownumber
FROM [order details] o
JOIN
(
SELECT TOP 10 o.productid n, o.quantity, COUNT(*) AS rownumber
FROM rs r
GROUP BY o.productid
ORDER BY COUNT(*) ASC
)
r ON r.productid = o.productid
WHERE r.rownumber in (8,9,10)
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
SELECT o.*, r.rownumber
FROM [order details] o
JOIN
(
SELECT TOP 10 o.productid n, o.quantity, COUNT(*) AS rownumber
FROM rs r
GROUP BY o.productid
ORDER BY COUNT(*) ASC
) b ON b.productid = o.productid
WHERE b.rownumber in (8,9,10)
Need to give the derived table a different alias
-
Thanks - tried it and I still get the invalid object name 'rs'. The inner query uses the alias of order detail in the outer query. Would that be the problem? I thought the inner query could do that but I might be wrong. Do I need to use order detail with a different alias?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Do you have 'rs' tabel ot view in Northwind DB?
-
I goofed on the 'rs'. should have been [order detail]. I now get a result but the count is the total number of records for each productid and since the inner query is executed once for each outer query record, I have it backwards.
I guess what this all boils down to is that I need a rowcount function that I can add to the derived table and be able to select rows based on the row number. Is there any way of doing that without using a cursor?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Try this script...
select top 10 OD.*, rownumber
from
(
select temp1.productid productid,
temp1.ORDERID ORDERID,
count(*) rownumber
from
(
SELECT a1.*
FROM [order details] a1
join
[order details] a2
on a1.productid = a2.productid and a1.ORDERID >= a2.ORDERID
) temp1
group by temp1.productid,temp1.ORDERID
having count(*) in (8,9,10)
) OD1
join
[order details] od
on OD1.productid = OD.productid and OD1.ORDERID = OD.ORDERID
Hope it will help
G.B
-
Thanks - I wound up using a temporary table.
Select top 10 rownum = identity(int,1,1),
productid, quantity
into #tmp
from [order details]
order by quantity desc
select * from #tmp
where rownum in (8,9,10)
drop table #tmp
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply