CODEWITHSUNDEEP
phpmysqlsql
randombits
randombits

Reputation: 48490

SQL return 1 row

Is it possible to take the following query and have it return 1 row of data instead of 5 rows?

 select count(rank_1) * 5 as 1st from fcs_player_rankings where rank_1 = "$wp_player['id']"
 UNION ALL
 select count(rank_2) * 4 as 2nd from fcs_player_rankings where rank_2 = "$wp_player['id']"
 UNION ALL
 select count(rank_3) * 3 as 3rd from fcs_player_rankings where rank_3 = "$wp_player['id']"
 UNION ALL
 select count(rank_4) * 2 as 4th from fcs_player_rankings where rank_4 = "$wp_player['id']"
 UNION ALL
 select count(rank_5) * 1 as 5th from fcs_player_rankings where rank_5 = "$wp_player['id']"

The table schema looks like the following:

mysql> desc fcs_player_rankings;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11) | NO   | MUL | NULL    |                |
| award_id | int(11) | NO   |     | NULL    |                |
| rank_1   | int(11) | YES  |     | NULL    |                |
| rank_2   | int(11) | YES  |     | NULL    |                |
| rank_3   | int(11) | YES  |     | NULL    |                |
| rank_4   | int(11) | YES  |     | NULL    |                |
| rank_5   | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

Right now this query would return rank_1, rank_2, rank_3, rank_4 & rank_5 for each id I provide in a separate row. Is there anyway to get all of the rankings in one row? the LIMIT 1 clause below doesn't logically solve this problem.

Upvotes: 0

Views: 124

Answers (2)

peterm
peterm

Reputation: 92845

Try conditional aggregation

SELECT SUM(rank_1 = "$wp_player['id']") * 5 `1st`,
       SUM(rank_2 = "$wp_player['id']") * 4 `2nd`,
       SUM(rank_3 = "$wp_player['id']") * 3 `3rd`,
       SUM(rank_4 = "$wp_player['id']") * 2 `4th`,
       SUM(rank_5 = "$wp_player['id']") * 1 `5th`
 FROM fcs_player_rankings

Here is SQLFiddle demo

Upvotes: 3

cameronjonesweb
cameronjonesweb

Reputation: 2526

Add the following to the end of your query LIMIT 1

Upvotes: 0

Related Questions