Help with SQL

[mistrpug]

You have this table:

Col1 Col2 Col3 Col4 Col5
5 26 32 75 84
35 5 45 78 8
65 65 84 26 6
72 26 35 5 84

You have to know how many times each number is returned, and order by the highest returns and then the number itself, getting as the answer:

84 - 3
26 - 3
5 - 3
65 - 2
35 -2
.
.
8 - 1
6 - 1

Help please. Thanks.

[TheTROLL]

Well, here it is (for SQL Server), but you have to punch the person who designed the database in the face. Hard.

/*
create table tester(col1 int, col2 int, col3 int, col4 int, col5 int)

insert into tester values(5,26,32,75,84)
insert into tester values(35, 5, 45, 78, 8)
insert into tester values(65, 65, 84, 26, 6)
insert into tester values(72, 26, 35, 5, 84)
*/

select col1, count(*) as occurs from
(select col1 from tester union all
select col2 from tester union all
select col3 from tester union all
select col4 from tester union all
select col5 from tester )as a group by col1 order by occurs desc,col1 desc

[mistrpug]

Thanks a lot. It's not a real DB, a buddy of mine at work came across this problem while trying to brush up on his SQL and neither of us could figure it out. Thanks again. Nice work.