MIN COUNT IGNORED - sql server

Hello
Want to ignore rows returned with lowest value

scenario .....

SELECT
C1,
C2,
COUNT(C3) AS C3
FROM T1
GROUP BY
C1,C2

ASSUME THIS RETURNS
C1        C2     C3
CPYA     REF1    45
CPYA     REF2    4
CPYB     REF7    56
CPYB     REF8    11

I WANT THE THE MIN COUNT IGNORED SO THE END RESULT WOULD BE AS FOLLOWS

C1        C2     C3
CPYA     REF1    45
CPYB     REF7    56
philsivyerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sridharthirunagariCommented:
I can't think of a simple solution to your query.
But this will give the result you wanted..but it is lengthy..

select C1,C2,C3
(
select C1,C2,C3, rank() over(partition by C1 order by C3 desc) AS seq
(
SELECT  C1,C2,COUNT(C3) AS C3
FROM T1
GROUP BY C1,C2
)
) where seq=1 ;

0
Rajkumar GsSoftware EngineerCommented:
If you mean, to exclude the lowest count, try this query.

Raj
WITH CTETABLE AS
(
	SELECT 
	C1,
	C2,
	COUNT(C3) AS C3
	FROM T1
	GROUP BY
	C1,C2
)
SELECT * FROM CTETABLE
where C3 > (SELECT min(C3) FROM  CTETABLE)

Open in new window

0
k_murli_krishnaCommented:
Try this:

SELECT C1, MAX(C3),MAX(C2) FROM(
SELECT
C1,
C2,
COUNT(C3) AS C3
FROM T1
GROUP BY
C1,C2)iq
GROUP BY C1;

0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

philsivyerAuthor Commented:
Where is this wrong?
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID)
(
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID),rank() over(partition by CLIENT order by COUNT(FUND_ID) desc) AS seq
(
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID)
FROM TABLE1

WHERE CLIENT = 'TEST'
AND KPI_ID = 16
GROUP BY CLIENT,KPI_ID,FUND_ID
)
) where seq=1
0
ralmadaCommented:
try like this
select C1, C2, C3 from 
(
	SELECT 	C1,
		C2,
		COUNT(C3) AS C3,
		row_number() over (partition by C1 order by Count(C3)) rn
	FROM T1
	GROUP BY C1,C2
) b 
where rn <> 1

Open in new window

0
ralmadaCommented:
so in your case
select * from 
(
SELECT 	CLIENT,
	KPI_ID,
	FUND_ID,
	COUNT(FUND_ID) as cfundid,
	row_number() over (partition by CLIENT order by COUNT(FUND_ID)) rn
FROM TABLE1
WHERE CLIENT = 'TEST'
AND KPI_ID = 16
GROUP BY CLIENT,KPI_ID,FUND_ID
) b
where rn<>1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sridharthirunagariCommented:
@Philsivyer
Where is this wrong?
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID)
(
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID),rank() over(partition by CLIENT order by COUNT(FUND_ID) desc) AS seq
(
SELECT CLIENT,KPI_ID,FUND_ID,COUNT(FUND_ID)
FROM TABLE1

WHERE CLIENT = 'TEST'
AND KPI_ID = 16
GROUP BY CLIENT,KPI_ID,FUND_ID
)
) where seq=1

------
Use an alias for COUNT(FUND_ID) field in the inner-most occurence...That will solve your problem..
0
philsivyerAuthor Commented:
Thanks Everybody

I used ralmada solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.