Aravinthan Posted August 20, 2009 Share Posted August 20, 2009 Hi, I am having kinda of an issue. Ok so what I want to do is, show the 8 teams from 1 conference. SO far so good.... Here is the code which does that: SELECT * FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` Order by ((`nhl_wins` *2) + `nhl_ot`) DESC But here is the tricky part, There is 3 division in 1 conference. So what I want is: The top 3 teams must not be in teh same division. For exemple: Division 1 Division 2 Division 3 So for exemple the team with the most points, in division 1 will be placed in the top 3 even if the a team that has more points but is 2nd Division 2. I dont know if I am explaining well... For people who know it, its the same as the NHL way of ranking.... Let say there is 15 teams, in 1 conference and 5 teams in each division. Teams(ordered by most points): Team 1 -> Division 1 Team 2 -> Division 2 Team 3 -> Division 2 Team 4 -> Division 3 Team 5 -> Division 1 Team 6 -> Division 2 Team 7 -> Division 1 Team 8 -> Division 3 Team 9 -> Division 2 ... So the rankings should be: Team 1 -> Division 1 Team 2 -> Division 2 Team 4 -> Division 3 ( Although Team 3 has more points, he cant be third, as there is already a team from division 2 in the top 3) .. The rest are orderd by points... Is there a way of doing this? Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/ Share on other sites More sharing options...
fenway Posted August 21, 2009 Share Posted August 21, 2009 Interesting... I'd find the top one in each division in one query, then the "logical" ordering skipping the first N, and union them. Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903225 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi Does this only apply to the first 3? In your example would Team 3 land up sorted to? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903256 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Hi, Keith, Yes I will post the complete standings of my exemple: Team 1 -> Division 1 Team 2 -> Division 2 Team 4 -> Division 3 Team 3 -> Division 2 Team 5 -> Divison 1 Team 6 -> Division 2 Team 7 -> Division 1 Team 8 -> Division 3 Team 9 -> Division 2 ... There is 15 teams in a conference, but you get the idea no? Thanks again for your help, Ara Fenway, I dont understand what you mean.... How to split the top 3 for the divisions ? Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903479 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi Been having a bit of a play. This does work to an extent, but will suffer if more than one team has the same "score" in a division. SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle FROM (SELECT team.*, ((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` ) a LEFT OUTER JOIN (SELECT division, MAX((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` GROUP BY division ORDER BY scoreMax LIMIT 3) b ON a.division = b.division AND a.scoreMax = b.scoreMax ORDER BY SortFiddle, a.scoreMax Basically does 2 select and joins the results. One select gets the score, the other gets the max score per division but used order by and limit to only select the first 3. Then uses a case statement to set a field to put the top 3 at the top. Might be a typo, but tried it on some crude tables and it works OK. So any typos are from putting it back to your table stuff. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903553 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Hi thanks so much, I got some questions. First: What do you mean by suffer? Because there is some teams that have the same points... Second: The Division is a colum in the table team_numbers.... So the code doesnt work... I tried changing: SELECT division To: SELECT teams_numbers.division But it returns empy... THanks for your help, Ara Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903559 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Did a few changes and this is what I got so far: SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle FROM (SELECT teams.*, ((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` ) a LEFT OUTER JOIN (SELECT division, MAX((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams_numbers LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` GROUP BY division ORDER BY scoreMax LIMIT 3) b ON a.division = b.division AND a.scoreMax = b.scoreMax ORDER BY SortFiddle, a.scoreMax But when I run it with Php My Admin I get this error: #1066 - Not unique table/alias: 'teams_numbers' Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903561 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi Think it is the other subselect you need to change (sorry, difficult to test without full tables) SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle FROM (SELECT team.*, teams_numbers.division, ((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` ) a LEFT OUTER JOIN (SELECT division, MAX((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `divison` != `divison` GROUP BY division ORDER BY scoreMax LIMIT 3) b ON a.division = b.division AND a.scoreMax = b.scoreMax ORDER BY SortFiddle, a.scoreMax The problem with multiples with the same score, is that if you had 2 teams with the same score which were the the highest score for that division then they would both be in the top 3 (which would now be the top 4). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903571 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Still no good... OK I will explain the Tables: Table: teams Table has all the statistics about each team. Wins, loss, points, etc Table: team_numbers Conference -> Est - Ouest Division -> The Division names ProName -> TeamName There is one mistake i saw, its in line 2, where it says Select team, it should teams. I changed that but it doesnt work Error: #1054 - Unknown column 'teams_numbers.division' in 'field list' And I changed every division to Division But still no good... Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903584 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Made a mistake, in my quoute it should say: #1054 - Unknown column 'teams_numbers.Division' in 'field list' The D from division is capital. Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903587 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 team_numbers: `ProName` varchar(100) NOT NULL, `Divison` varchar(50) NOT NULL, `Conference` varchar(50) NOT NULL, `ProAbv` varchar(10) NOT NULL, `ProId` int(11) NOT NULL, `FarmName` varchar(100) NOT NULL, `FarmId` int(11) NOT NULL INSERT INTO `teams_numbers` (`ProName`, `Divison`, `Conference`, `ProAbv`, `ProId`, `FarmName`, `FarmId`) VALUES ('Anaheim Ducks', 'Pacifique', 'Ouest', 'ANA', 1, 'Iowa Chops', 31), ('Atlanta Thrashers', 'Sud-Est', 'Est', 'ATL', 2, 'Chicago Wolves', 32), ('Boston Bruins', 'Nord-Est', 'Est', 'BOS', 3, 'Providence Bruins', 33), ('Buffalo Sabres', 'Nord-Est', 'Est', 'BUF', 4, 'Portland Pirates', 34), Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903590 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Sorry for posting too many at a time, But I fixed the all the typos. And it doesnt give me any errors, but it doesnt give me any result neither. Here is the fixed up code: SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle FROM (SELECT teams.*, teams_numbers.Divison, ((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `Divison` != `Divison` ) a LEFT OUTER JOIN (SELECT Divison, MAX((`nhl_wins` *2) + `nhl_ot`) AS scoreMax FROM teams LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName WHERE teams_numbers.Conference ='Est' AND `Divison` != `Divison` GROUP BY Divison ORDER BY scoreMax LIMIT 3) b ON a.Divison = b.Divison AND a.scoreMax = b.scoreMax ORDER BY SortFiddle, a.scoreMax Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903592 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi No problem. If you can post the declares I will try it. However just noticed that you check `division` != `division`. I thought the 2nd was using quotes rather than back ticks (ie, you were checking that the column division didn't have a value of division), but instead you are checking that the column division is not equal the the column division (which would block anything coming back). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903598 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Oh of course!!! I will post some values of the teams tables to if you want? What is a declares? teams: `id` int(11) NOT NULL auto_increment, `nhl_home_wins` int(5) NOT NULL, `nhl_home_loss` int(5) NOT NULL, `nhl_home_ties` int(5) NOT NULL, `nhl_home_ot` int(5) NOT NULL, `nhl_away_wins` int(5) NOT NULL, `nhl_away_loss` int(5) NOT NULL, `nhl_away_ties` int(5) NOT NULL, `nhl_away_ot` int(5) NOT NULL, `ahl_home_wins` int(5) NOT NULL, `ahl_home_loss` int(5) NOT NULL, `ahl_home_ties` int(5) NOT NULL, `ahl_home_ot` int(5) NOT NULL, `ahl_away_wins` int(5) NOT NULL, `ahl_away_loss` int(5) NOT NULL, `ahl_away_ties` int(5) NOT NULL, `ahl_away_ot` int(5) NOT NULL, `nhl_games_played` int(5) NOT NULL, `nhl_wins` int(5) NOT NULL, `nhl_loss` int(5) NOT NULL, `nhl_ot` int(5) NOT NULL, `nhl_ties` int(5) NOT NULL, `nhl_gf` int(5) NOT NULL, `nhl_ga` int(5) NOT NULL, `nhl_power_play_chances` int(5) NOT NULL, `nhl_pk_situations` int(5) NOT NULL, `nhl_ppgf` int(5) NOT NULL, `nhl_pp_ga` int(5) NOT NULL, `human/cpu_switch` int(5) NOT NULL, `playoff_switch` int(5) NOT NULL, `unknown_1` int(5) NOT NULL, `team_funds` int(5) NOT NULL, `team_budgets` int(11) NOT NULL, `ahl_games_played` int(5) NOT NULL, `ahl_wins` int(5) NOT NULL, `ahl_loss` int(5) NOT NULL, `ahl_ot` int(5) NOT NULL, `ahl_ties` int(5) NOT NULL, `ahl_gf` int(5) NOT NULL, `ahl_ga` int(5) NOT NULL, `ahl_power_play_chances` int(5) NOT NULL, `ahl_pk_situations` int(5) NOT NULL, `ahl_ppgf` int(5) NOT NULL, `ahl_ppga` int(5) NOT NULL, `line9` varchar(50) NOT NULL, `nhl_last10` varchar(50) NOT NULL, `ahl_last10` varchar(50) NOT NULL, `gm_name` varchar(50) NOT NULL, `line13` varchar(50) NOT NULL, `line14` varchar(50) NOT NULL, `line15` varchar(50) NOT NULL, `line16` varchar(50) NOT NULL, `line17` varchar(50) NOT NULL, `line18` varchar(50) NOT NULL, `line19` varchar(50) NOT NULL, `line20` varchar(50) NOT NULL, `most_goals` int(5) NOT NULL, `most_goals_name(season)` varchar(500) NOT NULL, `most_assists` int(5) NOT NULL, `most_assists_name(season)` varchar(500) NOT NULL, `most_points` int(5) NOT NULL, `most_points_name(season)` varchar(5000) NOT NULL, `most_rookie_points` int(5) NOT NULL, `most_rookie_points_name(season)` varchar(500) NOT NULL, `most_penalty` int(5) NOT NULL, `most_penalty_name(season)` varchar(500) NOT NULL, `stanley_cups` varchar(500) NOT NULL, `nhl_starting_goaler` int(5) NOT NULL, `nhl_backup_goaler` int(5) NOT NULL, `nhl_defense1` int(5) NOT NULL, `nhl_defense3` int(5) NOT NULL, `nhl_defense5` int(5) NOT NULL, `nhl_defense2` int(5) NOT NULL, `nhl_defense4` int(5) NOT NULL, `nhl_defense6` int(5) NOT NULL, `nhl_left_wing1` int(5) NOT NULL, `nhl_left_wing2` int(5) NOT NULL, `nhl_left_wing3` int(5) NOT NULL, `nhl_left_wing4` int(5) NOT NULL, `nhl_center1` int(5) NOT NULL, `nhl_center2` int(5) NOT NULL, `nhl_center3` int(5) NOT NULL, `nhl_center4` int(5) NOT NULL, `nhl_right_wing1` int(5) NOT NULL, `nhl_right_wing2` int(5) NOT NULL, `nhl_right_wing3` int(5) NOT NULL, `nhl_right_wing4` int(5) NOT NULL, `nhl_pp_defense1` int(5) NOT NULL, `nhl_pp_defense2` int(5) NOT NULL, `nhl_pp_lw1` int(5) NOT NULL, `nhl_pp_center1` int(5) NOT NULL, `nhl_pp_rw1` int(5) NOT NULL, `nhl_pp_defense3` int(5) NOT NULL, `nhl_pp_defense4` int(5) NOT NULL, `nhl_pp_lw2` int(5) NOT NULL, `nhl_pp_center2` int(5) NOT NULL, `nhl_pp_rw2` int(5) NOT NULL, `nhl_pk_defense1` int(5) NOT NULL, `nhl_pk_defense2` int(5) NOT NULL, `nhl_pk_forward1` int(5) NOT NULL, `nhl_pk_forward2` int(5) NOT NULL, `line66` varchar(50) NOT NULL, `nhl_pk_defense3` int(5) NOT NULL, `nhl_pk_defense4` int(5) NOT NULL, `nhl_pk_forward3` int(5) NOT NULL, `nhl_pk_forward4` int(5) NOT NULL, `line71` varchar(50) NOT NULL, `ahl_starting_goaler` int(5) NOT NULL, `ahl_backup_goaler` int(5) NOT NULL, `ahl_defense1` int(5) NOT NULL, `ahl_defense3` int(5) NOT NULL, `ahl_defense5` int(5) NOT NULL, `ahl_defense2` int(5) NOT NULL, `ahl_defense4` int(5) NOT NULL, `ahl_defense6` int(5) NOT NULL, `ahl_left_wing1` int(5) NOT NULL, `ahl_left_wing2` int(5) NOT NULL, `ahl_left_wing3` int(5) NOT NULL, `ahl_left_wing4` int(5) NOT NULL, `ahl_center1` int(5) NOT NULL, `ahl_center2` int(5) NOT NULL, `ahl_center3` int(5) NOT NULL, `ahl_center4` int(5) NOT NULL, `ahl_right_wing1` int(5) NOT NULL, `ahl_right_wing2` int(5) NOT NULL, `ahl_right_wing3` int(5) NOT NULL, `ahl_right_wing4` int(5) NOT NULL, `ahl_pp_defense1` int(5) NOT NULL, `ahl_pp_defense2` int(5) NOT NULL, `ahl_pp_lw1` int(5) NOT NULL, `ahl_pp_center1` int(5) NOT NULL, `ahl_pp_rw1` varchar(50) NOT NULL, `ahl_pp_defense3` int(5) NOT NULL, `ahl_pp_defense4` int(5) NOT NULL, `ahl_pp_lw2` int(5) NOT NULL, `ahl_pp_center2` int(5) NOT NULL, `ahl_pp_rw2` varchar(50) NOT NULL, `ahl_pk_defense1` int(5) NOT NULL, `ahl_pk_defense2` int(5) NOT NULL, `ahl_pk_forward1` int(5) NOT NULL, `ahl_pk_forward2` int(5) NOT NULL, `line106` varchar(50) NOT NULL, `ahl_pk_defense3` int(5) NOT NULL, `ahl_pk_defense4` int(5) NOT NULL, `ahl_pk_forward3` int(5) NOT NULL, `ahl_pk_forward4` int(5) NOT NULL, `line111` varchar(50) NOT NULL, `line1_tactics` int(5) NOT NULL, `line2_tactics` int(5) NOT NULL, `line3_tactics` int(5) NOT NULL, `line4_tactics` int(5) NOT NULL, `pp1_tactics` int(5) NOT NULL, `pp2_tactics` int(5) NOT NULL, `pk1_tactics` int(5) NOT NULL, `pk2_tactics` int(5) NOT NULL, `line113` varchar(100) NOT NULL, `line114` varchar(100) NOT NULL, `line115` varchar(100) NOT NULL, `line116` varchar(100) NOT NULL, `line117` varchar(100) NOT NULL, `line1_shift` int(5) NOT NULL, `line2_shift` int(5) NOT NULL, `line3_shift` int(5) NOT NULL, `line4_shift` int(5) NOT NULL, `pp1_shift` int(5) NOT NULL, `pp2_shift` int(5) NOT NULL, `pk1_shift` int(5) NOT NULL, `pk2_shift` int(5) NOT NULL, `line119` varchar(100) NOT NULL, `line120` varchar(100) NOT NULL, `line121` varchar(100) NOT NULL, `line122` varchar(100) NOT NULL, `line123` varchar(100) NOT NULL, `line124` varchar(100) NOT NULL, `line125` varchar(100) NOT NULL, `team_name` varchar(400) NOT NULL, PRIMARY KEY (`id`) INSERT INTO `teams` (`id`, `nhl_home_wins`, `nhl_home_loss`, `nhl_home_ties`, `nhl_home_ot`, `nhl_away_wins`, `nhl_away_loss`, `nhl_away_ties`, `nhl_away_ot`, `ahl_home_wins`, `ahl_home_loss`, `ahl_home_ties`, `ahl_home_ot`, `ahl_away_wins`, `ahl_away_loss`, `ahl_away_ties`, `ahl_away_ot`, `nhl_games_played`, `nhl_wins`, `nhl_loss`, `nhl_ot`, `nhl_ties`, `nhl_gf`, `nhl_ga`, `nhl_power_play_chances`, `nhl_pk_situations`, `nhl_ppgf`, `nhl_pp_ga`, `human/cpu_switch`, `playoff_switch`, `unknown_1`, `team_funds`, `team_budgets`, `ahl_games_played`, `ahl_wins`, `ahl_loss`, `ahl_ot`, `ahl_ties`, `ahl_gf`, `ahl_ga`, `ahl_power_play_chances`, `ahl_pk_situations`, `ahl_ppgf`, `ahl_ppga`, `line9`, `nhl_last10`, `ahl_last10`, `gm_name`, `line13`, `line14`, `line15`, `line16`, `line17`, `line18`, `line19`, `line20`, `most_goals`, `most_goals_name(season)`, `most_assists`, `most_assists_name(season)`, `most_points`, `most_points_name(season)`, `most_rookie_points`, `most_rookie_points_name(season)`, `most_penalty`, `most_penalty_name(season)`, `stanley_cups`, `nhl_starting_goaler`, `nhl_backup_goaler`, `nhl_defense1`, `nhl_defense3`, `nhl_defense5`, `nhl_defense2`, `nhl_defense4`, `nhl_defense6`, `nhl_left_wing1`, `nhl_left_wing2`, `nhl_left_wing3`, `nhl_left_wing4`, `nhl_center1`, `nhl_center2`, `nhl_center3`, `nhl_center4`, `nhl_right_wing1`, `nhl_right_wing2`, `nhl_right_wing3`, `nhl_right_wing4`, `nhl_pp_defense1`, `nhl_pp_defense2`, `nhl_pp_lw1`, `nhl_pp_center1`, `nhl_pp_rw1`, `nhl_pp_defense3`, `nhl_pp_defense4`, `nhl_pp_lw2`, `nhl_pp_center2`, `nhl_pp_rw2`, `nhl_pk_defense1`, `nhl_pk_defense2`, `nhl_pk_forward1`, `nhl_pk_forward2`, `line66`, `nhl_pk_defense3`, `nhl_pk_defense4`, `nhl_pk_forward3`, `nhl_pk_forward4`, `line71`, `ahl_starting_goaler`, `ahl_backup_goaler`, `ahl_defense1`, `ahl_defense3`, `ahl_defense5`, `ahl_defense2`, `ahl_defense4`, `ahl_defense6`, `ahl_left_wing1`, `ahl_left_wing2`, `ahl_left_wing3`, `ahl_left_wing4`, `ahl_center1`, `ahl_center2`, `ahl_center3`, `ahl_center4`, `ahl_right_wing1`, `ahl_right_wing2`, `ahl_right_wing3`, `ahl_right_wing4`, `ahl_pp_defense1`, `ahl_pp_defense2`, `ahl_pp_lw1`, `ahl_pp_center1`, `ahl_pp_rw1`, `ahl_pp_defense3`, `ahl_pp_defense4`, `ahl_pp_lw2`, `ahl_pp_center2`, `ahl_pp_rw2`, `ahl_pk_defense1`, `ahl_pk_defense2`, `ahl_pk_forward1`, `ahl_pk_forward2`, `line106`, `ahl_pk_defense3`, `ahl_pk_defense4`, `ahl_pk_forward3`, `ahl_pk_forward4`, `line111`, `line1_tactics`, `line2_tactics`, `line3_tactics`, `line4_tactics`, `pp1_tactics`, `pp2_tactics`, `pk1_tactics`, `pk2_tactics`, `line113`, `line114`, `line115`, `line116`, `line117`, `line1_shift`, `line2_shift`, `line3_shift`, `line4_shift`, `pp1_shift`, `pp2_shift`, `pk1_shift`, `pk2_shift`, `line119`, `line120`, `line121`, `line122`, `line123`, `line124`, `line125`, `team_name`) VALUES (1, 11, 4, 0, 2, 10, 9, 0, 1, 10, 2, 4, 1, 5, 10, 5, 0, 37, 21, 13, 3, 0, 88, 84, 123, 109, 24, 24, 1, 1, 10, -878176, 65740834, 37, 15, 12, 1, 9, 78, 70, 92, 86, 16, 12, '22 17 134', 'WWWWWLLLWOWWTLLWWWLWWLTTLOWLWLLLLTWWT', 'LOTWLLTWWWWTLTLWWLWWTTTLWWTWLLLLLTWWW', 'ANA', '263 263 263 977', '133 133 133 0', '1037 1037 1037 929', '230 230 230 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 52, 'Teemu Selanne (1997-98)', 62, 'Paul Kariya (1998-99)', 109, 'Teemu Selanne (1996-97)', 39, 'Paul Kariya (1994-95)', 285, 'Todd Ewen (1995-96)', '', 16, 524, 677, 2268, 1766, 623, 725, 2476, 633, 2908, 2841, 1594, 2575, 280, 687, 568, 1042, 1440, 1333, 386, 2268, 623, 633, 2575, 1042, 677, 1766, 1594, 568, 386, 623, 677, 280, 2575, '16', 725, 2476, 2841, 687, '16', 1434, 2974, 851, 396, 975, 2521, 1029, 1609, 684, 2307, 2781, 921, 2375, 139, 700, 2279, 2562, 2212, 3058, 341, 975, 396, 2307, 2375, '2562', 2521, 1029, 2781, 139, '3058', 851, 2521, 684, 341, '1434', 975, 396, 2212, 921, '1434', 1, 1, 4, 3, 0, 1, 4, 3, '1649 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 6, 6, 5, 2, 8, 4, 8, 4, '0 0 0 1 2', '0 0 50000 75000 3', '750000 6590000 3 4 5', '0 0 9450000 3300000 0', '-878176 65740834 65', '0 9358817', '623 278', 'Anaheim Ducks'), (2, 6, 9, 0, 3, 7, 10, 0, 3, 10, 6, 2, 0, 5, 14, 1, 0, 38, 13, 19, 6, 0, 91, 108, 137, 148, 25, 32, 1, 0, 40, 4508780, 52900927, 38, 15, 20, 0, 3, 87, 112, 57, 115, 9, 24, '19 19 100', 'LLLLLWLLLWLTLTLTTLLWLLWWLLWOLWTTLWWWWW', 'LLLLLWLLLLLTLLWWWLWWWWWWWWWLLTLLLWWLTL', 'ATL', '1035 1035 1035 997', '19 19 19 0', '1074 1074 1074 826', '0 0 0 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 32, 'Donald Audette (2000-01)', 47, 'Ray Ferraro (2000-01)', 50, 'Ray Ferraro (2000-01)', 27, 'Tomi Kallio (2000-01)', 226, 'Jeff Odgers (2000-01)', '', 3149, 729, 316, 2319, 272, 21, 1587, 1936, 113, 1612, 327, 65, 2725, 636, 616, 2234, 307, 885, 1047, 1796, 316, 1587, 1612, 636, 885, 2319, 1936, 113, 2725, 616, 272, 21, 636, 327, '3149', 2319, 316, 1796, 616, '3149', 1006, 1699, 1662, 1345, 1788, 3124, 3035, 1509, 328, 85, 2998, 3447, 2711, 2829, 3140, 2883, 1142, 25, 2747, 2955, 1662, 3124, 328, 2711, '25', 1509, 3035, 2998, 2829, '1142', 1662, 3124, 85, 2711, '1006', 1788, 1345, 2955, 328, '1006', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '0 0 0 1 2', '0 0 50000 75000 2', '400000 6940000 3 4 5', '0 0 9450000 3375000 2', '4508780 52900927 65', '0 8441215', '616 85', 'Atlanta Thrashers'), (3, 8, 9, 0, 3, 6, 11, 0, 2, 14, 5, 1, 0, 9, 7, 3, 0, 39, 14, 20, 5, 0, 86, 105, 143, 131, 32, 26, 1, 0, 10, -1769414, 59633054, 39, 23, 12, 0, 4, 80, 66, 98, 109, 15, 17, '22 18 102', 'LWLLLLLLWWWOWWWLLLLLWLLTLTWLLWTTWTLTLWL', 'WLWWLTWTWLWWTWWWLWWWLWWWLWLLTLWWWLLWWLW', 'BOS', '1030 1030 1030 1046', '451 451 451 0', '702 702 702 1033', '329 329 329 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 76, 'Phil Esposito (1970-71)', 102, 'Bobby Orr (1970-71)', 152, 'Phil Esposito (1970-71)', 102, 'Joe Juneau (1992-93)', 302, 'Jay Miller (1987-88)', '1929, 1939, 1941, 1970, 1972', 747, 1372, 263, 1330, 1757, 712, 2297, 559, 1639, 1285, 563, 2089, 872, 1836, 1754, 185, 93, 2574, 2620, 1726, 263, 712, 1639, 872, 93, 1757, 2297, 1285, 2574, 2089, 263, 712, 185, 2574, '747', 559, 1330, 1285, 1639, '747', 650, 2696, 514, 780, 3039, 347, 2184, 663, 281, 1247, 569, 1236, 1154, 2999, 2147, 58, 3299, 1462, 2662, 3298, 347, 663, 1247, 1154, '3299', 2184, 3039, 281, 58, '2662', 514, 347, 281, 1154, '650', 780, 2184, 1462, 569, '650', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '0 0 0 1 2', '0 0 50000 75000 3', '300000 7980000 3 4 5', '0 0 9450000 3450000 2', '-1769414 59633054 65', '0 10726138', '263 58', 'Boston Bruins'), (4, 13, 4, 0, 3, 7, 9, 0, 2, 9, 10, 1, 0, 4, 13, 1, 0, 38, 20, 13, 5, 0, 98, 103, 132, 125, 26, 28, 1, 1, 40, 7622660, 58255757, 38, 13, 23, 0, 2, 67, 104, 100, 92, 21, 20, '23 19 122', 'WLTWOWOLLLWTLTWLLTWWWWLLLLTWLWTTWWTWLT', 'LLLLLLLLLLWTWTLWWLWLLLLLWWWWLWLLWLWLLW', 'BUF', '635 635 635 647', '840 840 840 0', '408 408 408 1038', '201 201 201 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 76, 'Alexander Mogilny (1992-93)', 95, 'Pat LaFontaine (1992-93)', 148, 'Pat LaFontaine (1992-93)', 74, 'Rick Martin (1971-72)', 354, 'Rob Ray (1991-92)', '', 1016, 241, 818, 398, 488, 1584, 2050, 2058, 3489, 7, 2139, 2669, 183, 1795, 793, 661, 2156, 2569, 3478, 834, 818, 2050, 3489, 183, 2156, 2058, 398, 7, 1795, 2569, 1584, 2058, 2139, 793, '1016', 488, 2050, 2569, 661, '1016', 1218, 2689, 653, 2825, 353, 2, 3085, 3024, 2726, 3037, 948, 1557, 3387, 1413, 3011, 3380, 2235, 1480, 3158, 3248, 2825, 3085, 2726, 1413, '1480', 353, 2, 948, 3380, '3158', 653, 2, 2235, 2726, '1218', 353, 2825, 1480, 3248, '1218', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '50000 0 0 1 2', '0 0 50000 75000 2', '750000 7110000 3 4 5', '0 0 9450000 3375000 3', '7622660 58255757 65', '584997 12616436', '398 3246', 'Buffalo Sabres'), (5, 13, 3, 0, 3, 11, 8, 0, 1, 11, 6, 2, 0, 9, 7, 4, 0, 39, 24, 11, 4, 0, 83, 64, 113, 82, 15, 10, 1, 1, 40, 2444478, 66665556, 39, 20, 13, 0, 6, 97, 69, 92, 89, 22, 15, '23 13 156', 'WWWWWWWWWLWWWLWWWLWTWTLWLLLLTWTLLWOWLWT', 'WWLTWWTWWLWWLLLLTTWWLWWLLWWWWLTLLTWLWWW', 'CGY', '792 792 792 502', '465 465 465 0', '187 187 187 17', '642 642 642 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 66, 'Lanny McDonald (1982-83)', 82, 'Kent Nilsson (1980-81)', 131, 'Kent Nilsson (1980-81)', 92, 'Joe Nieuwendyk (1987-88)', 375, 'Tim Hunter (1988-89)', '1989', 2222, 674, 79, 3002, 1696, 2003, 2996, 3292, 587, 1416, 1044, 208, 329, 523, 1271, 2566, 120, 1763, 869, 285, 79, 2003, 587, 329, 120, 3292, 3002, 1416, 523, 1763, 79, 1696, 523, 120, '2222', 3292, 3002, 2566, 285, '2222', 2493, 3187, 654, 450, 1433, 555, 174, 3079, 979, 1801, 2964, 809, 1688, 3159, 3360, 2982, 989, 2834, 507, 2770, 555, 174, 979, 1688, '2834', 450, 1433, 1801, 3159, '989', 654, 555, 1688, 809, '2493', 1433, 450, 507, 989, '2493', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '0 0 0 1 2', '0 0 50000 0 2', '900000 7290000 3 4 5', '0 0 9450000 3525000 0', '2444478 66665556 65', '0 12662125', '120 809', 'Calgary Flames'), (6, 13, 6, 0, 1, 12, 5, 0, 2, 13, 7, 0, 0, 2, 17, 0, 0, 39, 25, 11, 3, 0, 103, 80, 120, 95, 24, 13, 1, 1, 0, 3739935, 67838499, 39, 15, 24, 0, 0, 84, 107, 80, 88, 13, 17, '21 19 103', 'WWWWLLLTLWLLWWLWWLOWLWLWWTWWWWWWWTWTWWL', 'LLWLLLLLLLLWLLLLWWWLWWLLWLLWLLWWWLWLLWW', 'CAR', '627 627 627 294', '524 524 524 0', '941 941 941 589', '835 835 835 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 56, 'Blaine Stoughton (1979-80)', 69, 'Ron Francis (1989-90', 105, 'Mike Rogers (1980-81)', 72, 'Sylvain Turgeon (1983-84)', 358, 'Torrie Robertson (1985-86)', '', 1580, 2236, 322, 2009, 604, 51, 1768, 154, 1759, 737, 1797, 1454, 1028, 2555, 19, 2605, 454, 302, 273, 2182, 51, 322, 1797, 2555, 454, 2009, 1768, 737, 1028, 302, 322, 604, 2555, 1759, '1580', 154, 1768, 302, 454, '1580', 369, 1401, 6, 1335, 3366, 2006, 2981, 1363, 1640, 515, 2419, 3372, 1530, 2763, 1062, 1856, 2514, 3185, 822, 1328, 6, 2006, 1640, 2763, '822', 1335, 1363, 2419, 1530, '3185', 6, 2981, 1640, 1530, '369', 1335, 2006, 515, 1062, '369', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '50000 0 0 1 2', '0 0 50000 75000 2', '600000 7870000 3 4 5', '0 0 9450000 3450000 2', '3739935 67838499 65', '531932 10532255', '454 3185', 'Carolina Hurricanes'), (7, 8, 6, 0, 2, 5, 15, 0, 2, 12, 2, 2, 0, 5, 10, 5, 2, 38, 13, 21, 4, 0, 86, 112, 135, 157, 21, 29, 1, 0, 10, 7939797, 63187699, 38, 17, 12, 2, 7, 80, 72, 115, 76, 14, 15, '20 15 137', 'WLWLWWLWLOTLLLTLLLTLLWTLTLLWLWLWLLLWWL', 'WTWWWWWLLTOTLWWWWLLOLLTWWLWWTWTLLLWTWL', 'CHI', '999 999 999 1027', '864 864 864 0', '1026 1026 1026 707', '374 374 374 0', '0 0 0', '0 0 0', '-20 -20', '-20 -20', 58, 'Bobby Hull (1968-69)', 87, 'Denis Savard (1987-88)', 131, 'Denis Savard (1987-88)', 90, 'Steve Larmer (1982-83)', 408, 'Mike Peluso (1991-92)', '1934, 1938, 1961', 1937, 2610, 1569, 2292, 1073, 2573, 103, 2827, 1646, 140, 68, 142, 2577, 393, 325, 1845, 502, 2112, 2850, 2126, 2292, 502, 1646, 2577, 2112, 1569, 103, 140, 325, 2850, 2573, 1569, 1845, 68, '1937', 103, 2292, 2126, 502, '1937', 629, 680, 1818, 1405, 47, 1585, 3326, 365, 142, 1892, 1054, 366, 2907, 1308, 1313, 3115, 1810, 2731, 2483, 266, 1818, 47, 142, 1313, '2731', 365, 3326, 366, 1308, '1810', 1585, 1405, 1054, 1892, '629', 1818, 3326, 2483, 142, '629', 1, 1, 4, 3, 0, 1, 4, 3, '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', '0 0 0 0 0 0 0 0 0 0', 7, 6, 5, 2, 8, 4, 8, 4, '0 0 0 1 2', '0 0 50000 0 2', '350000 6750000 3 4 5', '0 0 9450000 3450000 2', '7939797 63187699 65', '0 10810622', '271 1585', 'Chicago Blackhawks'), Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903604 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi Tried with that data and it does seem to work. Take out the bits that say AND `Divison` != `Divison` (it is preventing it from working, not sure what it is meant to be checking). One thing that is wrong is that the sortFiddle should be sorted DESC instead of ASC. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903624 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Hi yeah it works!! Thanks so much! Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903626 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 Too good to last... I changed the Conference to Ouest And the first team 1 is good. Also the 2nd team. But the third team is not good.. He has the same Division as the first, and the 4th placed team has more points than him.... Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903629 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi Could you post a bit more data (I removed the check for conferenace to get it to work with limited data). Also could you post the SQL you have just used. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903631 Share on other sites More sharing options...
Aravinthan Posted August 21, 2009 Author Share Posted August 21, 2009 I found the error... There was a typo in of the Division name. It works fine now!! Thanks so much for your help! Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903633 Share on other sites More sharing options...
kickstart Posted August 21, 2009 Share Posted August 21, 2009 Hi No problem. Glad I could help, and at least it makes me think. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903635 Share on other sites More sharing options...
fenway Posted August 22, 2009 Share Posted August 22, 2009 Interesting approach... wonder if the UNION logic would have been simpler, though. Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-903994 Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 Hi Possibly would have been, depending on how you excluded the top three from one of the unions. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/171225-solved-order-help/#findComment-904006 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.