How to get the max value of duplicate item

Hi Every Body,

I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.

example Door Emp_Id

10 1

10 2

10 3

11 4

12 5

11 1

this is the value in that table.......

I want get Which Door number maximum Access from the table.....

Thanks & Regards,

S.Sajan

It's unclear exactly what are you asking for or what are you trying to achieve. Can you share a small code snippet or give more information? how is the data stored? what do you mean by "maximum access"? etc.

|||I'll take a guess that you're trying to find the highest door number than a particular employee has access to.

To do this you need:

Code Snippet

Select max(Door) from [-Access] where Emp_id = @.Emp_id

If instead you're trying to work out which door the most employees have access to you need:

Code Snippet

Select top 1 Door, count(Door) from [-Access] group by Door order by count(Door) desc

There are probably better ways to do the latter, but I tried something similar on a pretty big table, 90,000 records, and it's pretty quick.

Sean

Edit: I tried it on Sql Server, but I think it's the same for Access.|||

hi Sean Fowler,

That is exactly correct....in that query...............Thanks..

I Have one more doubte....

Select Top 1 Door,Count(Door) from [Access] group by Door order by Count(Door)desc

Above query disply Door Number,Which is maximum access Door Number by Employee...

Suppose two Door has access same number of time ...In that time i want display the two Door Number

..this is my requirement....

thanks in advance..

Thanks & Regards,

S.Sajan

|||

Here it is,

Code Snippet

Create Table #dooraccess (

[Door] Varchar(100) ,

[Emp_Id] Varchar(100)

);

Insert Into #dooraccess Values('10','1');

Insert Into #dooraccess Values('10','2');

Insert Into #dooraccess Values('10','3');

Insert Into #dooraccess Values('11','4');

Insert Into #dooraccess Values('12','5');

Insert Into #dooraccess Values('11','1');

On SQL Server 2000/2005,

Code Snippet

Select Main.* from

(SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door) as Main

Join

(

Select Max(AccessCount) AccessCount from

(

SelectDoor, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door

) as Data

) as MaxAccess

On Main.AccessCount = MaxAccess.AccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) [AccessCount] from #dooraccess Group By Door Order By [AccessCount] Desc

On SQL Server 2005,

Code Snippet

;With CTE

as

(

Select Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess

)

, CTE2

as

(

Select * , Max([AccessCount]) Over() MaxAccessCount from CTE

)

Select * from CTE2 Where AccessCount = MaxAccessCount

--or

Code Snippet

Select Top 1 Door, Count([Emp_Id]) Over(partition By Door) [AccessCount] from #dooraccess order By [AccessCount] Desc

|||All good suggestions. You can also do this:

Code Snippet

Select Door, count(Door)
from [#dooraccess]
group by Door
having count(Door) = (select top 1 count(Door) from #dooraccess group by Door order by count(Door) desc)

Seansql