How to get top 2 rows with ties with a criteria

Question

Post reply

How to get top 2 rows with ties with a criteria

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Declare

@demo table (RewardID INT not null IDENTITY (1,1) Primary key clustered , CIFCode varchar(50) not null

, PopulationGroup varchar(50) not null, TotalPremium NUMERIC(18,2) NOT NULL)

INSERT

INTO @demo (CIFCode, PopulationGroup, TotalPremium)

SELECT

'15001', 'METRO', 62324000.00

UNION

ALL

SELECT

'15002', 'URBAN', 122052000.00

UNION

ALL

SELECT

'15003', 'SEMI URBAN', 22990000.00

UNION

ALL

SELECT

'15004', 'RURAL', 70880000.00

UNION

ALL

SELECT

'15006', 'URBAN', 143816000.00

UNION

ALL

SELECT

'15007', 'SEMI URBAN', 225428000.00

UNION

ALL

SELECT

'15008', 'RURAL', 93204000.00

UNION

ALL

SELECT

'15009', 'METRO', 44216000.00

UNION

ALL

SELECT

'15010', 'URBAN', 113038000.00

UNION

ALL

SELECT

'15011', 'SEMI URBAN', 8448000.00

UNION

ALL

SELECT

'15013', 'METRO', 73982000.00

UNION

ALL

SELECT

'15014', 'URBAN', 101988000.00

UNION

ALL

SELECT

'15015', 'SEMI URBAN', 172848000.00

UNION

ALL

--This is to add a tie to the data

SELECT

'15006', 'METRO', 62324000.00

Select

* from @demo

Select

* from @demo D1 where TotalPremium IN (Select TOP 2 TotalPremium from @demo D2 WHERE D1.PopulationGroup = D2.PopulationGroup ORDER BY TotalPremium DESC) ORDER BY D1.PopulationGroup, TotalPremium DESC

--I would extremely suggest that you add an index on the TotalPremium column if you want any kink of speed out of this query .

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Thanks a lot.

I used 4 different queries to get the desired results...and a lot more in other processes..

--Ramesh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

September 4, 2006 at 8:17 am

#658354

HTH .

Need anything else?

NS-349075 SSC Rookie Points: 39 More actions · Answer 4

This works great:

SELECT TOP 2 * WITH TIES FROM AnnualContestRewards

WHERE PopulationGroup = 'METRO'

ORDER BY CIFCode Desc

UNION ALL

SELECT TOP 2 * WITH TIES FROM AnnualContestRewards

WHERE PopulationGroup = 'URBAN'

ORDER BY CIFCode Desc

UNION ALL

SELECT TOP 2 * WITH TIES FROM AnnualContestRewards

WHERE PopulationGroup = 'SEMI URBAN'

ORDER BY CIFCode Desc

UNION ALL

SELECT TOP 2 * WITH TIES FROM AnnualContestRewards

WHERE PopulationGroup = 'RURAL'

ORDER BY CIFCode Desc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

How do you suggest to implement that with unlimited and unknown amount of Population groups?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Righly said... Even with limited & known no. of population groups, you cannot bury the features provided by the server........

--Ramesh