Mysql – Ordering by date and other columns

MySQLorder-by

I have a one table Q&A like below:

CREATE TABLE  `totaalconceptterreinonderhoud`.`acties` 
(
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `type` int(11) NOT NULL DEFAULT '3' COMMENT 'Actie types:\n1 = vraag\n2 = antwoord\n3 = afspraak\n4 = nieuws\n',
      `aanvang_actie` date NOT NULL COMMENT 'Aanvang afspraak datum + tijd',
      `start_tijd` time NOT NULL COMMENT 'Start tijd',
      `eind_tijd` time NOT NULL COMMENT 'Eind tijd',
      `proj_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Koppeling met Project',
      `onderwerp` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Onderwerp afspraak',
      `notitie` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Tekstuele omschrijving',
      `locatie` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `meld_aan_contacts` int(11) NOT NULL DEFAULT '1' COMMENT 'Melden aan contactpersonen',
      `koppel_id` int(11) NOT NULL DEFAULT '0' COMMENT 'koppelinng met andere id',
      `aangemaakt_door` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Wie heeft laatst aangepast',
      `aanmaak_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Wanneer aangemaakt',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Acties'

There are 4 types of record:

  • type=1 (question)
  • type=2 (answer)
  • 3 and 4 (not relevant now)

What I want is to list all questions with their answers below. The questions and answers have a koppel_id field that connects them. The ordering must be last asked question first, with answers right below.

Is this hard to do? Any help would be nice.

Best Answer

It would be something like the following. Please verify the syntax and functionality as I have not tested it. There might be a better way to do this.

SELECT * FROM
(
    SELECT
        @rownum1 := @rownum1 + 1 AS rownum,
        acties.*
    FROM
        acties
        INNER JOIN (SELECT @rownum1 := 0) AS rownum
    WHERE
        type = 1
    ORDER BY
        aanmaak_datum DESC
) AS question
UNION
SELECT * FROM
(
    SELECT question2.rownum, answers.*
    FROM acties AS answers
    INNER JOIN
    (
        SELECT
            @rownum2 := @rownum2 + 1 AS rownum,
            acties.*
        FROM
            acties
            INNER JOIN (SELECT @rownum2 := 0) AS rownum2
        WHERE
            type = 1
        ORDER BY
            aanmaak_datum DESC
    ) AS question2 ON answers.koppel_id = question2.koppel_id
    WHERE answers.type = 2
) as answer2
ORDER BY rownum, type
Related Question