/ Gists

Gists

On gists

Equal rank with same values

MySql MySql tricks

example.sql #

id   game_id   game_rank   score   team_id
-------------------------------------------
5    1         1           15      1
4    1         2           25      2
1    1         3           40      3
3    1         3           40      4
2    1         4           55      5
7    2         0           0       1
6    2         0           0       2


#1
SET @lastscore = 0;
SET @ordering = 0;

    UPDATE game_scores 
      SET
        game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
        , score = (@lastscore := score)
    WHERE game_id = 1
    ORDER BY score;
    
    
#2
UPDATE game_scores 
  CROSS JOIN ( SELECT @lastscore:=0, @ordering:=0) AS parameter
  SET
    game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
    , score = (@lastscore := score)
WHERE game_id = 1
ORDER BY score;


#3
SET @lastscore := 0;
SET @ordering := 0;

UPDATE game_scores SET 
IF(@lastscore = score, @ordering, @ordering := @ordering + 1),
game_rank = @ordering,
@lastscore := score
WHERE game_id = 1
ORDER BY score;
On gists

Multiple update with THEN / ELT

MySql MySql tricks

update.sql #

#1 
UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)


#2
On gists

Subcategories (limited level)

MySql MySql tricks

example.sql #

select l0.catID,
    concat(
      case when l5.catID is null then '' else concat(l5.category, '/') end
    , case when l4.catID is null then '' else concat(l4.category, '/') end
    , case when l3.catID is null then '' else concat(l3.category, '/') end
    , case when l2.catID is null then '' else concat(l2.category, '/') end
    , case when l1.catID is null then '' else concat(l1.category, '/') end
    , l0.category)
from catcat l0
left join catcat l1 on l0.parentID=l1.catID
left join catcat l2 on l1.parentID=l2.catID
left join catcat l3 on l2.parentID=l3.catID
left join catcat l4 on l3.parentID=l4.catID
left join catcat l5 on l4.parentID=l5.catID
On gists

Update/Delete with JOIN

MySql MySql tricks

example.sql #

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php




mysql> DELETE cds 
    -> FROM cds, artists 
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');

To DELETE from both tables:

mysql> DELETE cds, artist 
    -> FROM cds, artist
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');



===========



mysql> UPDATE cds 
    -> LEFT JOIN artists 
    -> USING (artistID)
    -> SET cds.artists = artists.Artist
    -> WHERE (cds.cdID = '2');


UPDATE videogallery_komentare t1 JOIN videogallery t2 ON t2.code = t1.code
SET t1.videoID = t2.ID
On gists

Find In set (multiple values are stoered like string in one column)

MySql MySql tricks

example.sql #

*******************************
Spojeni 2 tabulek, cisla zapasu jsou jako string v 1 sloupci, 
potrebujeme je vypsat ale pres IN( + subselect) to skutecne nejde, bere se to jako string
pomuze fce FIND_IN_SET a trosku netradicni pouziti
*******************************



/*stare spojeni*/

select z.* from 
_zapasy z, _souteze s 
where find_in_set(z.id,s.zapasy) and s.id=1


/* JOIN spojeni, 2 druhy */

select * from _zapasy z join _souteze s on s.id=1 where find_in_set(z.id,s.zapasy)
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) WHERE  s.id=1
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) AND s.id=1



-- 
-- Struktura tabulky `_zapasy`
-- 

CREATE TABLE `_zapasy` (
  `id` mediumint(9) NOT NULL auto_increment,
  `kdo` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- 
-- Vypisuji data pro tabulku `_zapasy`
-- 

INSERT INTO `_zapasy` (`id`, `kdo`) VALUES (1, 'brno - olomouc'),
(2, 'jablonec - plzen');



-- 
-- Struktura tabulky `_souteze`
-- 

CREATE TABLE `_souteze` (
  `id` int(11) default NULL,
  `nazev` varchar(255) default NULL,
  `zapasy` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 
-- Vypisuji data pro tabulku `_souteze`
-- 

INSERT INTO `_souteze` (`id`, `nazev`, `zapasy`) VALUES (1, 'test-liga', '1,2');
On gists

IN(multiple values)

MySql MySql tricks

in.sql #

select ID_osoby
from   co_si_pucil
where  (ID_polozky,druh) in ((11043,'kniha'),(9443,'film'))
group  by ID_osoby
having count(distinct ID_polozky, druh) = 2 -- pocet hledanych kombinaci
On gists

Soccer table on the fly.sql

•
MySql MySql tricks

table.sql #

select 
    team, 
    count(*) played, 
    count(case when goalsfor > goalsagainst then 1 end) wins, 
    count(case when goalsagainst> goalsfor then 1 end) lost, 
    count(case when goalsfor = goalsagainst then 1 end) draws, 
    sum(goalsfor) goalsfor, 
    sum(goalsagainst) goalsagainst, 
    sum(goalsfor) - sum(goalsagainst) goal_diff,
    sum(
          case when goalsfor > goalsagainst then 3 else 0 end 
        + case when goalsfor = goalsagainst then 1 else 0 end
    ) score 
from (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores
) a 
group by team
order by score desc, goal_diff desc;
On gists

first/least/max row per group in SQL

MySql MySql tricks

baron-schwartz.sql #

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+


#1 self join
select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;


#2 correlated subquery, less efficient
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);


+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
| cherry | bing     |  2.55 | 
+--------+----------+-------+


###################################
# $n from each
###################################

#1  self join?
select type, variety, price
from fruits
where (
   select count(*) from fruits as f
   where f.type = fruits.type and f.price <= fruits.price
) <= 2;



#2 union
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)


#3a variables 
set @num := 0, @type := '';

select type, variety, price
from (
   select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
  from fruits
  order by type, price
) as x where x.row_number <= 2;


#3b variables with force without parent subquery
set @num := 0, @type := '';

select type, variety, price,
      @num := if(@type = type, @num + 1, 1) as row_number,
      @type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;
On gists

Ranking variables (2 ways)

MySql MySql tricks

ranking.sql #

#1 way
set @radek=0; 
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;


#2 way
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t



SET @poradi:=0,@poradib:=0;
SELECT @poradi:=@poradi+1, @poradi as poradi, IF(id=201, @poradib:=@poradi, 0), id, name FROM table ORDER BY `name` DESC;
SELECT @poradib;
On gists

Ranking via variables

MySql MySql tricks

ranking.sql #

set @type := '', @num := 1;

select type, variety, price, row_number
from (
   select type, variety, price,
       @num := if(@type = type, @num + 1, 1) as row_number,
       @type := type as dummy
   from fruits
) as x
where row_number <= 2;

+--------+----------+-------+------------+
| type   | variety  | price | row_number |
+--------+----------+-------+------------+
| apple  | gala     |  2.79 |          1 | 
| apple  | fuji     |  0.24 |          2 | 
| orange | valencia |  3.59 |          1 | 
| orange | navel    |  9.36 |          2 | 
| pear   | bradford |  6.05 |          1 | 
| pear   | bartlett |  2.14 |          2 | 
| cherry | bing     |  2.55 |          1 | 
| cherry | chelan   |  6.33 |          2 | 
+--------+----------+-------+------------+