| Author |
 Topic  |
|
|
pnpsql
Posting Yak Master
India
244 Posts |
 Posted - 03/14/2013 : 02:16:29
|
i have a table
t_chrg
custid chargid
1 10
1 11
1 12
2 11
2 11
2 15
3 11
i want to get custid that have maximum count of charges are applied. in this case this is 1 and 2
challenge everything |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1707 Posts |
Posted - 03/14/2013 : 02:21:29
|
DECLARE @t_chrg TABLE(custid INT, chargid INT)
INSERT INTO @t_chrg VALUES(1, 10), (1, 11 ), (1, 12 ), (2, 11), (2, 11), (2, 15), (3, 11)
SELECT TOP 1 WITH TIES custid, COUNT(*) cnt
FROM @t_chrg
GROUP BY custid
ORDER BY cnt DESC
--
Chandu |
 |
|
|
pnpsql
Posting Yak Master
India
244 Posts |
Posted - 03/14/2013 : 02:29:50
|
any other clue
challenge everything |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1707 Posts |
Posted - 03/14/2013 : 02:34:13
|
quote:
Originally posted by pnpsql
any other clue
challenge everything
whats the problem with above query....?
On which version you are working?
--
Chandu |
|
|
|
pnpsql
Posting Yak Master
India
244 Posts |
Posted - 03/14/2013 : 02:40:52
|
using 2008 but not want to use ties. please suggest
challenge everything |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1707 Posts |
Posted - 03/14/2013 : 03:09:07
|
;with CTE AS (SELECT custid, COUNT(*) cnt FROM @t_chrg GROUP BY custid)
SELECT custid
FROM CTE
WHERE cnt = (SELECT MAX(cnt) FROM CTE)
--
Chandu |
|
|
| |
Topic |
|
query help
