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

Ramesh Saive

SSC-Insane

Points: 24275
More actions
September 4, 2006 at 6:07 am

#115332

Hi all,
Let's define the schema of the table..
AnnualContestRewards ( RewardID INT PK IDENT , CIFCode V(50) N.N. , PopulationGroup V(50) N.N. , TotalPremium NUM(18,2) )
This table contains the following data...
1 15001 METRO 62324000.00
2 15002 URBAN 122052000.00
3 15003 SEMI URBAN 22990000.00
4 15004 RURAL 70880000.00
5 15006 URBAN 143816000.00
6 15007 SEMI URBAN 225428000.00
7 15008 RURAL 93204000.00
8 15009 METRO 44216000.00
9 15010 URBAN 113038000.00
10 15011 SEMI URBAN 8448000.00
11 15013 METRO 73982000.00
12 15014 URBAN 101988000.00
13 15015 SEMI URBAN 172848000.00
I want the top 2 CIFs with ties i.e. CIFCode along with PopulationGroup & TotalPremim for each population group.
For ref. the output that i need, for above input is
11 15013 METRO 73982000
1 15001 METRO 62324000
7 15008 RURAL 93204000
4 15004 RURAL 70880000
6 15007 SEMI URBAN 225428000
13 15015 SEMI URBAN 172848000
5 15006 URBAN 143816000
2 15002 URBAN 122052000
thanks in advance for any help....
--Ramesh

--Ramesh

Viewing 7 posts - 1 through 6 (of 6 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