why does both the block number is shown when I want to show the block with maximum departments?

Question

I have a table department with 3 column(department_name , department_id, department_block_number) so I want to fetch the department_block_number in which maximum number of department is located ? I have two department_block_number 303, 202 and each has 4 and 2 departments respectively? how can i do it?

select q1.department_block_number , max(c)
(select department_block_number , count(department_id)as c from department group by department_block_number)q1,
group by department_block_number ;

select q1.department_block_number , max(c)
(select department_block_number , count(department_id)as c from department group by department_block_number)q1,
group by department_block_number ;

Now i want to show only 303 as it is the block number with maximum departments in it but my query is showing both 303, 202 please help me . If you know some other way so that i can fetch the result so please help

Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using — John Conde, Apr 12 '19 at 15:57
So add the Oracle tag. Don't just put a bunch of random tags. — Eric, Apr 12 '19 at 16:32

Gordon Linoff · Answer 1 · 2019-04-12T16:43:56.867

1

In standard SQL if you are looking for one row, you would do:

select d.department_block_number, count(*)
from department d
group by d.department_block_number
order by count(*) desc
fetch first 1 row only;

Some databases spell fetch first 1 row only as limit 1 or select top (1) or in even more arcane ways.

In older versions of Oracle (fetch is supported in 12c+), you can do:

select department_block_number, cnt
from (select d.department_block_number, count(*) as cnt
      from department d
      group by d.department_block_number
      order by count(*) desc
     ) d
where rownum = 1;

edited Apr 12 '19 at 16:43

answered Apr 12 '19 at 15:59

Gordon Linoff

1,242,037
58
646
786

Its seems you didn't understood my question i want to display the block number which has maximum departments in it – sAnskar modi Apr 12 '19 at 16:02
@sAnskarmodi . .. That is what this does. – Gordon Linoff Apr 12 '19 at 16:08
and i want to show only block 303 but it is showing both 303 and 202 – sAnskar modi Apr 12 '19 at 16:14
1

@sAnskarmodi That's the reason you should tag properly. It's useless if someone post a solution, but you can't use it. – Eric Apr 12 '19 at 16:34

why does both the block number is shown when I want to show the block with maximum departments?

1 Answers1