May 6, 2005 at 10:18 pm
My table has a field called category it's a foreign key.
I need to create a query that returns the top three records from each category. The number of categories is unknown.
Desired results
Category Field1 Field2 Field3
1 blah blah blah
1 blah blah blah
1 blah blah blah
2 blah blah blah
2 blah blah blah
2 blah blah blah
3 blah blah blah
3 blah blah blah
3 blah blah blah
Any help is appreciated! Thanks,
Paully
May 7, 2005 at 2:59 pm
Maybe this can get you started. I'm sure someone else can come up with a more elegant solution.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'tblCategory'
AND type = 'U')
DROP TABLE tblCategory
GO
Create table tblCategory
(
id int,
field1 varchar(20),
field2 varchar(20)
)
Go
Insert into tblCategory
Select 1, '1aaa', '1aaa'
Union All
Select 1, '1bbb', '1bbb'
Union All
Select 1, '1ccc', '1ccc'
Union All
Select 1 , '1ddd', '1ddd'
Union All
Select 2, '2aaa', '2aaa'
Union All
Select 2, '2bbb', '2bbb'
Union All
Select 2, '2ccc', '2ccc'
Union All
Select 2 , '2ddd', '2ddd'
----------------------------------
----------------------------------
Select c1.id, c1.field1, c1.field2
From tblCategory c1
Where c1.field1 IN (Select top 3 field1
From tblCategory c2
Where c1.id = c2.id
Order By c2.id, field1, field2)
And c1.field2 IN (Select top 3 field2
From tblCategory c2
Where c1.id = c2.id
Order By c2.id, field1, field2)
Order by c1.id, c1.field1, c1.field2
-----------------------------------
drop table tblCategory
-----------------------------------
ID Field1 Field2
----------------
1 1aaa 1aaa
1 1bbb 1bbb
1 1ccc 1ccc
2 2aaa 2aaa
2 2bbb 2bbb
2 2ccc 2ccc
May 7, 2005 at 5:59 pm
This is by no means elegant but since the # of categories is UNKNOWN I cannot think you can avoid cursors.....something like this in a stored procedure/UDF ?!?!
CREATE PROCEDURE Get_Top3
AS
DECLARE @CategoryID Int
CREATE TABLE #TempCategory
(
Category Int,
Field1 VarChar(50),
Field2 VarChar(50),
Field3 VarChar(50)
)
BEGIN
DECLARE ScrollCategory CURSOR
FOR
SELECT DISTINCT Category FROM tblCategory
OPEN ScrollCategory
FETCH ScrollCategory INTO @CategoryID
WHILE @@FETCH_STATUS = 0--while there are rows to be fetched
BEGIN
INSERT INTO #TempCategory
(SELECT TOP 3 * FROM tblCategory
WHERE Category = @CategoryID
ORDER BY ???)
FETCH ScrollCategory INTO @CategoryID
END
END
CLOSE ScrollCategory
DEALLOCATE ScrollCategory
--now get all your rows from temp table
SELECT * FROM #TempCategory ORDER BY CategoryID
**ASCII stupid question, get a stupid ANSI !!!**
May 8, 2005 at 11:05 am
Thanks...I was hoping for one of those elegant non-cursor solutions, but I was thinking last night too, this might NEED a cursor.
BTW, that code was spot on...I was pretty much able to use it exactly as written...THANK YOU sushila!
May 8, 2005 at 4:59 pm
Great! Thanks for feedback!
**ASCII stupid question, get a stupid ANSI !!!**
May 8, 2005 at 9:13 pm
I'm sure you can come up with a solution that doesn't use cursors.
If you make a UDF that looks to see if a row is in the top 3 for that category, something like:
create function dbo.uf_istop3(@rowid int, @cat int) returns int as
begin
if @rowid in (select top 3 rowid from tblCategory c where c.categoryid = @cat order by ???)
return (1)
else
return (0)
end
Then you can do something like:
select * from tblCategory
where dbo.uf_istop3(rowid, categoryid) = 1
and have it return what you're after...
PS: I'm just using 'rowid', because I'm not sure of the best way of referring to your rows. You should have some sort of ID field in your table.
Does this do what you're after?
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 8, 2005 at 9:24 pm
Sorry - small typo. The 'else' statement in there makes the function invalid. If you remove it, then it's fine. Better still, surround 'return(1)' in 'begin' and 'end'.
Here's an example I tried using the Northwind database. It works well, looking for each employees top 3 orders.
create function dbo.uf_istop3(@orderid int, @employeeid int) returns int as
begin
if @orderid in (
select top 3 o.orderid
from orders o
join
[Order Details] od
on od.orderid = o.orderid
where o.employeeid = @employeeid
group by o.orderid
order by sum(od.unitprice * od.quantity * (1.0-od.discount))
)
begin
return (1)
end
return (0)
end
go
grant execute on dbo.uf_istop3 to public
go
select *
from employees e
join
orders o
on o.employeeid = e.employeeid
where dbo.uf_istop3(o.orderid, e.employeeid) = 1
order by e.lastname
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 9, 2005 at 1:44 am
If your table has a single-column primary key (column pk in the following SQL), then it should be simple:
/*fictitious table for testing*/
CREATE TABLE tbl (pk int identity primary key, category int, field1 int, field2 int)
INSERT INTO tbl (category, field1,field2) VALUES (1,1,1)
INSERT INTO tbl (category, field1,field2) VALUES (1,2,3)
INSERT INTO tbl (category, field1,field2) VALUES (1,5,4)
INSERT INTO tbl (category, field1,field2) VALUES (1,4,6)
INSERT INTO tbl (category, field1,field2) VALUES (1,8,1)
INSERT INTO tbl (category, field1,field2) VALUES (2,2,2)
INSERT INTO tbl (category, field1,field2) VALUES (2,1,1)
INSERT INTO tbl (category, field1,field2) VALUES (2,1,4)
INSERT INTO tbl (category, field1,field2) VALUES (2,7,1)
INSERT INTO tbl (category, field1,field2) VALUES (2,3,9)
INSERT INTO tbl (category, field1,field2) VALUES (3,1,8)
INSERT INTO tbl (category, field1,field2) VALUES (3,3,1)
INSERT INTO tbl (category, field1,field2) VALUES (3,2,4)
INSERT INTO tbl (category, field1,field2) VALUES (3,1,7)
INSERT INTO tbl (category, field1,field2) VALUES (3,5,1)
INSERT INTO tbl (category, field1,field2) VALUES (3,1,1)
/*for each category, find the top 3 entries based on field2*/
SELECT category, field1, field2
FROM tbl t
WHERE t.pk IN (SELECT TOP 3 pk from tbl WHERE tbl.category = t.category ORDER BY field2 desc)
ORDER BY category, field2 desc
Just another occasion where identity column will help; if you don't have it, it will be more complicated, but it still should be possible.
May 9, 2005 at 2:05 am
See if this helps:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=180032#bm180450
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 9, 2005 at 7:05 am
Paully - Vladan's t-sql is your "elegant" solution....
**ASCII stupid question, get a stupid ANSI !!!**
May 9, 2005 at 8:49 am
Totally elegant...Thanks it worked great. Sushila--Thanks too, while I was waiting for the elegant solution I was still able to test the page.
Paully21
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply