March 11, 2005 at 2:18 pm
Hi there, I have a question for how to display the 4 most happened values in a table disregard which the value is stored in which column. The table has a simple structure with columns RowId (pk), Value1 INT, Value2 INT, Value3 INT, and Value4 INT. Value in those Value columns are between 1-99 and NOT NULL. My question is how do I able to find out which 4 numbers are the most happened values in the table. Is this make sense? I have tried UNION Value1,...Value4 but the result is not what I am looking for. Is there anyone can help?
Thanks,
AC
March 11, 2005 at 4:01 pm
Please post create table and insert statements.
create table MyTable
( Value1 INT, Value2 INT, Value3 INT, Value4 INT )
go
insert into MyTable
(Value1 , Value2 , Value3 , Value4 )
select 1, 1, 1, 1 union all
select 2, 2, 2, 2 union all
select 2, 2, 2, 2 union all
select 3, 3, 3, 3 union all
select 3, 3, 3, 3 union all
select 3, 3, 3, 3 union all
select 4, 4, 4, 4 union all
select 4, 4, 4, 4 union all
select 4, 4, 4, 4 union all
select 4, 4, 4, 4 union all
select 5, 5, 5, 5 union all
select 5, 5, 5, 5 union all
select 5, 5, 5, 5 union all
select 5, 5, 5, 5 union all
select 5, 5, 5, 5
select top 4 MyValue, MyCount
from(selectNormalizedTable.MyValue, count(*) as MyCount
from (select Value1 from MyTable union all
select Value2 from MyTable union all
select Value3 from MyTable union all
select Value4 from MyTable
) as NormalizedTable (MyValue)
group by NormalizedTable.MyValue
) as MyValueCounts (MyValue, MyCount)
order by Mycount desc
SQL = Scarcely Qualifies as a Language
March 11, 2005 at 4:27 pm
Many thanks, it is working. Have a nice weekend!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply