July 27, 2005 at 2:47 pm
This may be a common question, but is there a way to query for the most common value (mode) in a column???
The situation is that I have a list of orders that I need to group by address, and each address is supposed to have a specific name with it. But in the application where our employees enter the name for the address they are allowed to enter it different for each order which means mistakes can be made. When I group these orders per address I want to show the most common name along with the address. Is this possible???
Thanks,
Adam
July 27, 2005 at 2:55 pm
Would some flavor of this work?
SELECT top 1 count( column ) , column
from table
where [your conditions]
order by count( column ) desc
July 27, 2005 at 4:44 pm
do you mean that they might enter something like "32 laurel lane"; "laurel ln 32" etc..?! just making sure I understand the question!!
**ASCII stupid question, get a stupid ANSI !!!**
July 28, 2005 at 7:33 am
Yeah......Let's say they enter five records
1 - Ted, 2 - Ted,3 - Larry,4 - Ted,5 - Bob
I want to return "Ted" because "Ted" occurred the most often. I would need it to occur in a function of some sort so that I can collect it in a select query that finds each unique address.
Thanks
July 28, 2005 at 8:00 am
okay - can't test this right now - but I think Merrill had your answer ?!?! - is this what you want ?!
SELECT A.* FROM tblTest A
INNER JOIN
(SELECT TOP 1 COUNT(empName) Total, empName
FROM tblTest
GROUP BY empName
ORDER BY COUNT(empName) DESC)B
ON A.empName = B.empName
**ASCII stupid question, get a stupid ANSI !!!**
August 1, 2005 at 1:08 pm
Sorry about the slow reply, that did work, but it was not fast enough for the application I am creating (the app has a large amount of data in it). I just had to build a work around in the application to allow the user to view additional data if needed.
Thanks for the help!
Adam
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply