sql qustion

[yuri1992]

hello to all , i have problem ,
my sql table called Kings
and its look like this
ID Game1 Game2 Game3 Game4 Game5 Game6 Game 7 Date Username
1 213 222 323 232 522 212 122 3.4.09 user1
2 214 212 325 432 262 222 292 3.4.09 user55
3 253 222 523 832 224 232 228 3.4.09 user77
4 273 212 723 233 222 225 272 3.4.09 user5
5 223 272 363 123 122 262 622 3.4.09 user2

I need to pull from the table ten numbers that appeared most in
columns game1 game2 game3 game4 game5 game6 game7 and show them in Sort order
how i do this ?

[yuri1992]

the output will be
1)222 - show 4 time
2)212 - show 2 time
and Onwards

[Ramy Mahrous]

I recommend you to normalize your table first then we can engage with SQL.

[urtrivedi]

Ramy is right, that you must create another table for detail and then you need to join them.

But if you like to continue with this design then you have to use union keyword to get your result. I Dont know about the performance,

select game,count(*) total  from
	(select game1 as game from kings
	union 
	select game2 as game from kings
	union 
	select game3 as game from kings
	union 
	select game4 as game from kings
	union 
	select game5 as game from kings
	union 
	select game6 as game from kings
	union 
	select game7 as game from kings
	)
group by game order by count(*) desc

[mail2saion]

LOOKS INTERESTING. TO DO THAT I HAVE CREATED A TABLE LIKE:

id	int	Checked
game1	int	Checked
game2	int	Checked
game3	int	Checked

THEN ENTRY FEW DATA LIKE:
1 100 101 102
2 200 201 202
3 300 100 101

HERE 101 shoud be the TOP am i right. To do that i write the below SQL:

SELECT TOP 10 PopGame,SUM(counter) Occurance FROM(
SELECT GAME1 PopGame,COUNT(*) counter FROM GAMES
GROUP BY GAME1
UNION ALL 
SELECT GAME2,COUNT(*) FROM GAMES
GROUP BY GAME2
UNION ALL
SELECT GAME3,COUNT(*) FROM GAMES
GROUP BY GAME3) tbl
GROUP BY PopGame ORDER BY Occurance DESC

[yuri1992]

i tried to put the code you two give me and its didnt work

$query22 = mysql_query("SELECT TOP 10 PopGame,SUM(counter) Occurance FROM(
				SELECT Game1 PopGame,COUNT(*) counter FROM Kings
		GROUP BY Game1
		UNION ALL 
		SELECT Game2,COUNT(*) FROM Kings
		GROUP BY Game2
		UNION ALL
		SELECT Game3,COUNT(*) FROM Kings
		GROUP BY Game3) tbl
		GROUP BY PopGame ORDER BY Occurance DESC
");

$num=1;
while ($rowwe = mysql_fetch_array($query22))
{

[urtrivedi]

You first execute this query directly on mysql, using phpmyadmin or any other tool, check for syntax error. when you find proper final query then try it in php.

[Ramy Mahrous]

You can discuss that in MySQL forum http://www.daniweb.com/forums/forum126.html please help moderators to keep the Daniweb organized.