SQL - One row for each name

Question

Trying to utilise the technique from https://stackoverflow.com/a/123481 I'm attempting to retrieve one row for each name, with a particular sort, namely the top/first row after sorting in descending order by active, created, then prid. However the active column may contain numberic or NULL values, which is causing a duplicate in the name=bat case. Any help would be greatly appreciated.

Source table:

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 1    | bat   | NULL   | 3       |
| 2    | bat   | 1      | 2       |
| 3    | bat   | 2      | 1       |
| 4    | bat   | 3      | 0       |
| 5    | noise | NULL   | 2       |
| 6    | noise | NULL   | 1       |
| 7    | cup   | NULL   | 0       |
| 8    | cup   | NULL   | 0       |
| 9    | egg   | 4      | 4       |
| 10   | egg   | 4      | 2       |
+------+-------+--------+---------+

Desired result:

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 9    | egg   | 4      | 4       |
| 4    | bat   | 3      | 0       |
| 5    | noise | NULL   | 2       |
| 8    | cup   | NULL   | 0       |
+------+-------+--------+---------+

SQL:

SELECT p1.*
FROM source_table p1
LEFT JOIN source_table p2 ON (
  p1.name = p2.name
  AND (
    p1.active < p2.active
    OR (
      (p1.active = p2.active OR (p1.active IS NULL AND p2.active IS NULL))
      AND (
        p1.created < p2.created
        OR (
          p1.created = p2.created AND p1.prid < p2.prid
        )
      )
    )
  )
)
WHERE p2.prid IS NULL
ORDER BY p1.active DESC, p1.created DESC, p1.prid DESC

Actual result:

+------+-------+--------+---------+
| prid | name  | active | created |
+------+-------+--------+---------+
| 9    | egg   | 4      | 4       |
| 4    | bat   | 3      | 0       |
| 1    | bat   | NULL   | 3       |
| 5    | noise | NULL   | 2       |
| 8    | cup   | NULL   | 0       |
+------+-------+--------+---------+

@Gordon Linoff

Thanks for the help, I try to use the second version with the indexes (name, active, created, prid) and (active, created, prid), however it's being quite slow.

This takes 1 second, returns the right results, but in the wrong order:

SELECT t1.prid
FROM source_table t1
WHERE t1.prid = (
  SELECT t2.prid
  FROM source_table t2
  WHERE t2.name = t1.name
  ORDER BY t2.active DESC, t2.created DESC, t2.prid DESC
  LIMIT 1
)
LIMIT 50

And this takes 55 seconds:

SELECT t1.prid
FROM source_table t1
WHERE t1.prid = (
  SELECT t2.prid
  FROM source_table t2
  WHERE t2.name = t1.name
  ORDER BY t2.active DESC, t2.created DESC, t2.prid DESC
  LIMIT 1
)
ORDER BY t1.active DESC, t1.created DESC, t1.prid DESC
LIMIT 50

And really I need LIMIT 500, any ideas?

@Rick James

SQL Fiddle link: http://sqlfiddle.com/#!9/f9b39/2/0

I was trying to keep it database agnostic, but I guess if I can get something working for MySQL variants that would be a good start. — Jason, Jul 12 '18 at 12:24

Gordon Linoff · Answer 1 · 2018-07-14T02:17:43.140

Use the ANSI-standard row_number() function:

select t.*
from (select t.*,
             row_number() over (partition by name
                                order by active desc, created desc, pid desc
                               ) as seqnum
      from source_table t
     ) t
where seqnum = 1;

The above works in MySQL 8+ and MariaDB 10.2+. In earlier versions, you can do:

select t.*
from source_table t
where t.prid = (select t2.prid
                from source_table t2
                where t2.name = t.name
                order by t2.active desc, t2.created desc, t2.pid desc
                limit 1
               );

For performance, you want an index on source_table(name, actdive desc, created desc, pid desc, prid).

Thanks for the help, I updated my question with the results of your suggestion, any tips on how to resolve it? — Jason, Jul 12 '18 at 14:04

Rick James · Answer 2 · 2018-07-17T18:22:11.750

0

ORDER BY IFNULL(active,  -1) DESC,
         IFNULL(created, -1) DESC,
         IFNULL(pid,     -1) DESC,

Full Stmt

SELECT  prid, name, active, created
    FROM  
        ( SELECT  GROUP_CONCAT(prid
                    ORDER BY  IFNULL(active,  -1) DESC,
                              IFNULL(created, -1) DESC,
                              IFNULL(prid,    -1) DESC 
                              ) AS ids
            FROM  source_table
            GROUP BY  name 
        ) AS s1
    JOIN  source_table AS s2
        ON s2.prid = SUBSTRING_INDEX(s1.ids, ',', 1)
    ORDER BY  IFNULL(active,  -1) DESC,
              IFNULL(created, -1) DESC,
              IFNULL(prid,    -1) DESC ;

edited Jul 17 '18 at 18:22

answered Jul 12 '18 at 21:03

Rick James

135,179
13
127
222

Please provide sqlfiddle so we can experiment. – Rick James Jul 13 '18 at 15:34
@Jason - OK, I had to fold in some "groupwise max" code to get it to work. – Rick James Jul 17 '18 at 00:21
Tried your updated code, but it suffers from the same issue as Gordon Linoff's did, that being it returns the correct results, but in the wrong order. – Jason Jul 17 '18 at 12:27
@Jason - Oops, I needed to add a copy of the `ORDER BY`. Try again. – Rick James Jul 17 '18 at 18:23

SQL - One row for each name

2 Answers2