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?
 
ralmadaConnect With a Mentor Commented:
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
 
sridharthirunagariConnect With a Mentor Commented:
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 GsConnect With a Mentor Software 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
k_murli_krishnaConnect With a Mentor Commented:
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
 
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
 
sridharthirunagariConnect With a Mentor Commented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.