By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,462 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,462 IT Pros & Developers. It's quick & easy.

Help

P: 18
for ex one table is there like charge ,fields are chargeid,chargename

chargeid's are having duplicates like chargeid
101
101
102
.......
1.I want to disply the colmns of chargeid's and no.of times like(in desc order)

chargeid no. of times
101 2
102 ----

2.I want to display only those chargeid's are maximum times repeated
Feb 8 '08 #1
Share this Question
Share on Google+
5 Replies


amitpatel66
Expert 100+
P: 2,367
for ex one table is there like charge ,fields are chargeid,chargename

chargeid's are having duplicates like chargeid
101
101
102
.......
1.I want to disply the colmns of chargeid's and no.of times like(in desc order)

chargeid no. of times
101 2
102 ----

2.I want to display only those chargeid's are maximum times repeated
Could you please post what you have tried so far??
Feb 8 '08 #2

P: 18
Could you please post what you have tried so far??
select chargeid,count(*) from charges group by chargeid having count(*)>1 order by 2

this query displays chargeid's and corresponding number of times for the chargeid now i want to disply only maximum number of chargeid's list in this table (suppose 101 chargeid is maximum used in the table)

like

chargeid no. of times
101 1
101 2

(or)

chargename chargeid

101
101
Feb 8 '08 #3

deepuv04
Expert 100+
P: 227
select chargeid,count(*) from charges group by chargeid having count(*)>1 order by 2

this query displays chargeid's and corresponding number of times for the chargeid now i want to disply only maximum number of chargeid's list in this table (suppose 101 chargeid is maximum used in the table)

like

chargeid no. of times
101 1
101 2

(or)

chargename chargeid

101
101

hi,
if you want to get only the maximum number of times repeated chargid then
use the following query

thanks

select top 1 chargeid,count(*) from charges group by chargeid having count(*)>1 order by 2 desc
Feb 8 '08 #4

P: 18
now i want to disply only the maximum no. of chargeid's list(individual list) like below ex:


ex:

chargeid chargename
101 ------- xxx
101 ------- yyy
101 -------- aaa
101 ------- kkk
Feb 9 '08 #5

deepuv04
Expert 100+
P: 227
now i want to disply only the maximum no. of chargeid's list(individual list) like below ex:


ex:

chargeid chargename
101 ------- xxx
101 ------- yyy
101 -------- aaa
101 ------- kkk

SELECT charges.chargeid ,charges.chargename from charges INNER JOIN
( select top 1 chargeid,count(*) from charges group by chargeid having count(*)>1 order by 2 ) AS C1
on charges.chargId = c1.chargid
Feb 11 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.