Mysql Subselect by row count

Question

I'm looking to take the following table and represent the data in the format outlined below.

All the Type A dates as column Date 1 and Type B dates as column Date 2. Type A will always have more rows than Type B so for any missing rows I need null to be included instead.

I not sure what type of transformation this is so much question may be misleading.

The query can be written in mysql >=5.7

Type A -> Date 1
Type B -> Date 2

------------------     
|    Table       |
-----------------|
| type |  date   |
|   A  | 2019-01 |
|   A  | 2019-02 |
|   A  | 2019-03 |
|   A  | 2019-04 |
|   B  | 2018-10 |
|   B  | 2018-11 |
------------------

I want to transform the data to the following:

------------------------
|   Date 1   |   Date 2 |
|   2019-01  |  2018-10 |
|   2019-02  |  2018-11 |
|   2019-03  |  null    |
|   2019-04  |  null    |
-------------------------

Could you explain _why_ you need this? Because honestly, what you're asking for looks quite awkward in a relational database, and it seems like taking a step back to look at the bigger picture might be a good idea. (Basically, in a relational database, all fields in a single row are normally assumed to be connected somehow. In your case, it seems like there's no connection between e.g. `2019-01` and `2018-10` except that they're the first dates of type A and type B respectively.) — Ilmari Karonen, Jan 23 '19 at 22:54
I agree the table is not set up in the most optimal way for this query but it is out of my control. I'm just looking for possible solutions before going down the path of restructuring. — Tom, Jan 23 '19 at 22:58
There is no relatable id but I was wondering if maybe row number would be sufficient. — Tom, Jan 23 '19 at 23:01
@Stanzi1791: It's almost certainly _possible_; just do two [enumerated subqueries](https://stackoverflow.com/questions/3047789/how-to-enumerate-returned-rows-in-sql), one for each group, and (outer) join the results together on the enumerated column. But it's still an ugly hack, because SQL isn't meant to be used like this. — Ilmari Karonen, Jan 23 '19 at 23:05

Tik · Accepted Answer · 2019-01-23T23:24:07.380

3

try something like this, mysql doesn't have a row number or rank function which is a pain but can be achieved by something like this

SET @row_number_a = 0;
SET @row_number_b = 0;

SELECT * FROM
(SELECT
(@row_number_a:=@row_number_a + 1) AS rn, date
FROM test 
WHERE type = 'A'
ORDER BY date DESC) a

LEFT JOIN 
(SELECT
(@row_number_b:=@row_number_b + 1) AS rn, date
FROM test 
WHERE type = 'B'
ORDER BY date DESC) b
ON a.rn = b.rn

edited Jan 23 '19 at 23:24

answered Jan 23 '19 at 23:07

Tik

822
6
14

1

I think you've made a typo in the subquery, the alias should be rn_b – Ass3mbler Jan 23 '19 at 23:15
yep there are a couple i just loaded up mysql will edit – Tik Jan 23 '19 at 23:21

Nick · Answer 2 · 2019-01-23T23:32:23.623

In MySQL 8+ you can use ROW_NUMBER() to order rows and then join the B dates to the corresponding A dates. Note that if you might have more B dates than A dates, you need a FULL JOIN which is not available in MySQL but can be emulated by a UNION of a LEFT JOIN and a RIGHT JOIN. If that is not the case, you can remove the part of the query from the UNION onwards.

WITH a AS (SELECT ROW_NUMBER() OVER (ORDER BY `date`) AS `row`, `date`
           FROM table1
           WHERE type = 'A'),
     b AS (SELECT ROW_NUMBER() OVER (ORDER BY `date`) AS `row`, `date`
           FROM table1
           WHERE type = 'B')
SELECT a.date AS `Date 1`, b.date AS `Date 2`
FROM a
LEFT JOIN b ON b.row = a.row
UNION
SELECT a.date AS `Date 1`, b.date AS `Date 2`
FROM a
RIGHT JOIN b ON b.row = a.row

Output

Date 1      Date 2
2019-01     2018-10
2019-02     2018-11
2019-03     
2019-04

Demo on dbfiddle (which also shows the need for a FULL JOIN with more B dates than A dates).

Mysql Subselect by row count

2 Answers2