On gists

Top N per group - 4 ways

MySql MySQL - ArtfulSoftware MySql - advanced

0-structure.sql Raw #

DROP TABLE IF EXISTS test;
CREATE TABLE test( id INT, entrydate DATE );
INSERT INTO test VALUES
( 1, '2007-5-01' ),( 1, '2007-5-02' ),
( 1, '2007-5-03' ),( 1, '2007-5-04' ),
( 1, '2007-5-05' ),( 1, '2007-5-06' ),
( 2, '2007-6-01' ),( 2, '2007-6-02' ),
( 2, '2007-6-03' ),( 2, '2007-6-04' ),
( 3, '2007-7-01' ),( 3, '2007-7-02' ),
( 3, '2007-7-03' );


-- expected result
+------+------------+------+
| id   | entrydate  | rank |
+------+------------+------+
|    1 | 2007-05-01 |    1 |
|    1 | 2007-05-02 |    2 |
|    2 | 2007-06-01 |    1 |
|    2 | 2007-06-02 |    2 |
|    3 | 2007-07-01 |    1 |
|    3 | 2007-07-02 |    2 |
+------+------------+------+

1-window.sql Raw #

SELECT id, entrydate, `rank`
FROM ( 
  SELECT 
    id, entrydate, 
    ROW_NUMBER() 
      OVER( PARTITION BY id 
            ORDER BY id,entrydate
          ) AS `rank`
  FROM test 
  ORDER BY id 
) AS tmp 
WHERE tmp.`rank` <= 2 
ORDER BY id, entrydate;

2-custom-variables.sql Raw #

SELECT id, entrydate, `rank`
FROM (
  SELECT
    id, entrydate,
    IF( @prev <> id, 
        @rownum := 1, 
        @rownum := @rownum+1 
      ) AS `rank`,
    @prev := id
  FROM test
  JOIN (SELECT @rownum:=NULL,@prev:=0) AS r
  ORDER BY id, entrydate
) AS tmp
WHERE tmp.`rank` <= 2
ORDER BY id, entrydate;

3-self-join.sql Raw #

SELECT t1.id, t1.entrydate, count(*) AS earlier
FROM test AS t1
JOIN test AS t2 
  ON t1.id=t2.id 
  AND t1.entrydate >= t2.entrydate
GROUP BY t1.id, t1.entrydate
HAVING earlier <= 2;

4-SP-procedure.sql Raw #

BEGIN
  DECLARE curdone, vid INT DEFAULT 0;
  DECLARE idcur CURSOR FOR 
    SELECT DISTINCT id FROM test;
  DECLARE CONTINUE HANDLER 
    FOR SQLSTATE '02000' SET curdone = 1;
  DROP TEMPORARY TABLE IF EXISTS earliers;
  CREATE TEMPORARY TABLE earliers( 
    id INT, entrydate DATE);
    
    /* 1#
  SET @sql = 
  'INSERT INTO earliers 
    SELECT id,entrydate 
    FROM test 
    WHERE id=? ORDER BY  entrydate LIMIT 2';
    
    */
  OPEN idcur;
  REPEAT
    FETCH idcur INTO vid;
   
    IF NOT curdone THEN
      BEGIN
      /* 1#
        SET @vid = vid;
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @vid;
        DROP PREPARE stmt;
        */
        
      /* # 2 or directly ;-) */
		INSERT INTO earliers 
		SELECT id,entrydate 
		FROM test 
		WHERE id= vid ORDER BY  entrydate LIMIT 2;
        
      END;
    END IF;
  UNTIL curdone END REPEAT;
  CLOSE idcur;
  SELECT * FROM earliers 
  ORDER BY id,entrydate;
  DROP TEMPORARY TABLE earliers;
END


CALL  listearliers();

5-6-ranked-corelated-with-border Raw #

/* AKA DJPW */

SELECT * FROM skupiny
JOIN
	(

		SELECT 
		sk.pismeno, 
		(
			select id
			from   skupiny s
			WHERE s.skupina = sk.pismeno
			order  by id desc
			LIMIT 0, 1
		) hranice 
		FROM skupiny_kategorie sk
		
	) tmp ON tmp.pismeno = skupiny.skupina 
			AND skupiny.id >= tmp.hranice -- nebo >= IFNULL(h.hranice, skupiny.id)
			
			
			
/* AKA DJPW, having funguje i na nezgrupovane? */
select s.*,
       @n := @n * (@last_idk = s.skupina) + 1 n,
       @last_idk := s.skupina k
from   skupiny s, (select @n := 0, @last_idk := '') t
having n <= 5
order  by s.skupina, s.id desc


/* AKA 2# */
SELECT id, skupina, RANK
FROM
(

  SELECT
    id, skupina,
    IF( @prev <> skupina, 
        @rownum := 1, 
        @rownum := @rownum+1 
      ) AS `rank`,
    @prev := skupina
  FROM skupiny
  JOIN (SELECT @rownum:=NULL,@prev:=0) AS r
  ORDER BY id DESC, skupina
  
) tmp WHERE tmp.rank <= 2
ORDER BY skupina, id DESC



/*
-- Exportování struktury pro tabulka sandbox.skupiny
CREATE TABLE IF NOT EXISTS `skupiny` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `skupina` varchar(50) DEFAULT NULL,
  `cislo` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;

/*!40000 ALTER TABLE `skupiny` DISABLE KEYS */;
INSERT INTO `skupiny` (`id`, `skupina`, `cislo`) VALUES
	(1, 'A', 1),
	(2, 'A', 2),
	(3, 'A', 3),
	(4, 'A', 4),
	(5, 'A', 5),
	(6, 'B', 10),
	(7, 'B', 20),
	(8, 'B', 30),
	(9, 'B', 40),
	(10, 'B', 50),
	(11, 'C', 100),
	(12, 'C', 200),
	(13, 'C', 300),
	(14, 'C', 400),
	(15, 'C', 500);
/*!40000 ALTER TABLE `skupiny` ENABLE KEYS */;

-- Exportování struktury pro tabulka sandbox.skupiny_kategorie
CREATE TABLE IF NOT EXISTS `skupiny_kategorie` (
  `id` int(11) DEFAULT NULL,
  `pismeno` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- Exportování dat pro tabulku sandbox.skupiny_kategorie: 3 rows
/*!40000 ALTER TABLE `skupiny_kategorie` DISABLE KEYS */;
INSERT INTO `skupiny_kategorie` (`id`, `pismeno`) VALUES
	(1, 'A'),
	(2, 'B'),
	(3, 'C');
/*!40000 ALTER TABLE `skupiny_kategorie` ENABLE KEYS */;