Two Plus Two Older Archives

Two Plus Two Older Archives (http://archives2.twoplustwo.com/index.php)
-   Computer Technical Help (http://archives2.twoplustwo.com/forumdisplay.php?f=46)
-   -   Help with SQL (http://archives2.twoplustwo.com/showthread.php?t=303206)

mistrpug 07-29-2005 10:20 AM

Help with SQL
 
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 07-29-2005 10:32 AM

Re: Help with SQL
 
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 07-29-2005 10:35 AM

Re: Help with SQL
 
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.


All times are GMT -4. The time now is 01:17 AM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.