select query get max count based on id field.

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

SQL Server 2005 Forums

Transact-SQL (2005)

select query get max count based on id field.

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-02-09 : 15:56:55

I have a table actions, moduleid is the int field.

There can be multiple records with same moduleid.

How can i get the max rows info for a single id.

select max(count) from tab_actions where based on moduleid and moduletype='UT'

Thank you very much for teh info.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-09 : 16:02:39

[CODE]select max(a.countIDs)
from (
select moduleID, count(*) countIDs
from tab_actions
group by moduleID
) a[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

mymatrix
Starting Member

24 Posts

Posted - 2010-02-10 : 00:46:15

To Add further...

select max(a.countIDs)
from (
select moduleID, count(*) countIDs
from tab_actions
group by moduleID
having moduletype='UT'
) a

**************************************

Even my blood group says be -ve to all the negatives.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 01:24:39

i think what you're asking for is


select reqd columns
from
(
select row_number() over(partition by moduleid order by pk desc) as seq,*
from yourtable
)t
where seq=1

pk is primary key of your table

------------------------------------------------------------------------------------------------------
SQL Server MVP

askininfo
Starting Member

1 Post

Posted - 2010-06-26 : 06:15:17

--To Find MAX (Count())
Select UnitID,COUNT(UnitID) AS Counts FROM d_UnitArchive
Group By UnitID
Having COUNT(UnitID) >= (Select MAX(Counts)AS uCount From (Select UnitID,COUNT(UnitID) AS Counts FROM d_UnitArchive Group By UnitID) AS TempData)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources