Top 3 in each group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Top 3 in each group

Author

Topic

LGOOLSBY
Starting Member

4 Posts

Posted - 2007-03-19 : 14:29:39

I need to query for the top 3 records in each group. For example, If I run a query for

SELECT PAYER, CODE, PRICE, ALLOWABLE FROM CODERATES ORDER BY PAYER, CODE, ALLOWABLE

I will be returned a list of some 70000 records. For Each CODE withinn a PAYER, I want to see the TOP 3 ALLOWABLES (a dollar amount.)

I can use TOP 3 and get just the TOP 3 allowables but I want the top 3 allowables for each CODE within a PAYER. I hope that I have not lost you in my wording. I would love to know how to do this using SQL.

Any help?

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 14:36:32

[code]-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Payer VARCHAR(20), Code VARCHAR(20), Price MONEY, Allowable INT, Grp INT)

INSERT @Stage (Payer, Code, Price, Allowable)
SELECT Payer,
Code,
Price,
Allowable
FROM CodeRates
ORDER BY Payer,
Code,
Allowable DESC

-- Initialize ORDERING
DECLARE @PrevPayer VARCHAR(20),
@PrevCode VARCHAR(20),
@Grp INT

SELECT @PrevPayer = CHAR(9),
@PrevCode = CHAR(9)

UPDATE s
SET @Grp = Grp = CASE WHEN Payer = @PrevPayer AND Code = @PrevCode THEN @Grp + 1 ELSE 1 END,
@PrevPayer = Payer,
@PrevCode = Code
FROM @Stage AS s

-- Show the expected output
SELECT Payer,
Code,
Price,
Allowable
FROM @Stage
WHERE Grp BETWEEN 1 AND 3
ORDER BY RecID[/code]
Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 14:47:35

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 15:09:35

Up for a speed test on 70000 records?

I would estimate the method above to take about less than 10 seconds including staging.
And I think #2 blog point will take more than 100 seconds.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-19 : 15:24:24

true...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 17:42:54

The difference was not that much!

On average, the #2 point was half as fast as the other method.
And #2 method also only updated one sequency...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 17:45:01

[code]SET NOCOUNT ON

-- Prepare sample data
DECLARE @Sample TABLE
(
Col1 VARCHAR(2),
Col2 VARCHAR(2)
)

DECLARE @i INT

SELECT @i = 700000

WHILE @i > 0
BEGIN
INSERT @Sample (Col1, Col2)
SELECT LEFT(c, 2),
RIGHT(c, 2)
FROM (
SELECT SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 10, 4) AS c
) AS x

SET @i = @i - 1
END

declare @dt datetime, @d int
set @dt = current_timestamp

-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Col1 VARCHAR(2), Col1Seq INT, Col2 VARCHAR(2), Col2Seq INT)

INSERT @Stage (Col1, Col2)
SELECT Col1,
Col2
FROM @Sample
ORDER BY Col1,
Col2

-- Initialize ORDERING
DECLARE @PrevCol1 VARCHAR(2),
@PrevCol2 VARCHAR(2),
@Col1Seq INT,
@Col2Seq INT

SELECT @PrevCol1 = CHAR(9),
@PrevCol2 = CHAR(9)

UPDATE s
SET @Col1Seq = Col1Seq = CASE WHEN Col1 = @PrevCol1 THEN @Col1Seq ELSE ISNULL(@Col1Seq, 0) + 1 END,
@Col2Seq = Col2Seq = CASE WHEN Col1 = @PrevCol1 THEN @Col2Seq + 1 ELSE 1 END,
@PrevCol1 = Col1,
@PrevCol2 = Col2
FROM @Stage AS s

-- Show the expected output
select @d = count(*) from (
SELECT Col1,
Col1Seq,
Col2,
Col2Seq
FROM @Stage
WHERE Col2Seq BETWEEN 1 AND 3
--ORDER BY RecID
) as e

select datediff(ms, @dt, current_timestamp) / 1000.0

set @dt = current_timestamp

create table #s (col1 varchar(2), col2 varchar(2))

insert #s (col1, col2)
select col1, col2
from @sample order by col1, col2

CREATE clustered INDEX IX_1 ON #s (Col1, Col2)

declare @n int
Set @n = 3

select @d = count(*) from (
select col1, col2, xyz
from (
Select a.col1, a.col2, (Select count(1) From #s as x Where x.col1 = A.col1 and x.col2 <= a.col2 ) as xyz
From #s as A) as d
Where xyz <= @n
-- Order By col1, col2
) as e

select datediff(ms, @dt, current_timestamp) / 1000.0

drop table #s[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources