Select HIGHEST count ONLY? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select HIGHEST count ONLY?

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-03 : 21:02:09

Hi i get the following output table when i run this querry.
How could i display only the highest count Area_STUDY_DESCR?
(so only Business)

OUA_ID     AREA_STUDY_DESCR   MAX COUNT
112294	  Arts                  3
112294	  Business              4



SELECT E.OUA_ID,D.AREA_STUDY_DESCR, count(D.AREA_STUDY_DESCR) as MAX_COUNT

FROM OUA_ENROLMENT_MV E

JOIN OUA_UNIT_VW U  

                    on E.SUBJECT_CDE = U.SUBJECT_CDE
                    AND E.YEAR = U.YEAR
                    AND E.CATALOGUE_NO = U.CATALOGUE_NO
       
JOIN OUA_DISCIPLINE_VW D 
                    ON U.OUA_DISCIPLINE_CDE = D.OUA_DISCIPLINE_CDE
WHERE E.OUA_ID = '112294'

GROUP BY E.OUA_ID, D.AREA_STUDY_DESCR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 21:33:19

[code]
SELECT TOP 1 ...
ORDER BY MAX_COUNT DESC
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-03 : 21:38:11

Thanks,
i am using oracle so had to used "rownum = 1"

Another question.
If the count is equal (so Business and Art have the same number)
How could i point to a different table to retrive the most recent record from a particular column in that tabel?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 21:41:31

then you will need to JOIN to that table and then add that column in the ORDER BY

ORDER BY MAX_COUNT DESC, <another column>

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-03 : 21:48:12

Yes but how do i write the If count equal function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 22:19:42

something like this


SELECT *
FROM
(
SELECT E.OUA_ID,D.AREA_STUDY_DESCR, count(D.AREA_STUDY_DESCR) as MAX_COUNT
FROM OUA_ENROLMENT_MV E
JOIN OUA_UNIT_VW U  
                    on E.SUBJECT_CDE = U.SUBJECT_CDE
                    AND E.YEAR = U.YEAR
                    AND E.CATALOGUE_NO = U.CATALOGUE_NO      
JOIN OUA_DISCIPLINE_VW D 
                    ON U.OUA_DISCIPLINE_CDE = D.OUA_DISCIPLINE_CDE
WHERE E.OUA_ID = '112294'
GROUP BY E.OUA_ID, D.AREA_STUDY_DESCR
) T
INNER JOIN ANOTHER_TABLE A  ON T.<COLUMN NAME> = A.<COLUMN NAME>
ORDER BY ...

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-03 : 22:37:13

Back to my previous question.
Instead of TOP 1 and rownum=1
is there a statemnt which is specificly for to picking the highest count?
(not using TOP 1 and rownum=1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-03 : 23:00:52

you can also use row_number()


select *
from
(
    select rn = row_number() over (order by ...)
    from   . . . 
)t
where t.rn = 1

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-04-04 : 00:22:25

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources