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

How to get row count from an inner query

Hi All,

I have the following SQL query:

select temp.emp_id, temp.rownum

from

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

) temp

where temp.rownum <=10

group by temp.emp_id

I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.

Hi,

I wrote something similar in here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2188269&SiteID=1

But the problem may be, that the Number of Rows in the outer Statement dosent match to the number of rows in the inner Statement.

Best Regards

Raimund

|||

How about this,

Code Block

with temp

as

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

)

,countfinder

as

(

select count(*) as [rowcount] from temp

)

select temp.emp_id, temp.rownum, (select [rowcount] from countfinder) [totalrowcount] from temp

where temp.rownum <=10 group by temp.emp_id

|||Brilliant.....this was a very novel way of doing it. Thank you very much. I will try out the same in my implementation.