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
