Query to show most frequently appeared number

[new2unix]

Hi,

Need help in creating a query showing numbers appearing most frequently under each column, for example:

DATE COLUMN1 COLUMN2
1/1/2004 2 5
1/2/2004 3 2
1/3/2004 4 3
1/4/2004 2 1
1/5/2004 2 5
1/6/2004 3 4

The report would show:

COLUMN1 COLUMN2
2 5

Thanks,

Mike

 

[TonyJollans]

Hi new2unix,

Try this ..

[blue][tt]SELECT TOP 1 COLUMN1,
COLUMN2

FROM YourTable

GROUP BY COLUMN1,
COLUMN2

ORDER BY COUNT(*) DESC;[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[new2unix]

Thanks, Tony. Based on your example, I found the following portion worked for me:

SELECT TOP 1 COLUMN1, COLUMN2
FROM YourTable;


Mike

 

[TonyJollans]

Hi Mike,

Your change simply finds the first record in whatever order they come. In this example it happens to be the one you want but with different data it probably won't be.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[JonFer]

What if two numbers appear with the same frequency in a column? Do you want to see both numbers or does it matter?