I need to retrieve unique yet truncated part numbers, with their description values being conditionally determined.

DATA:
Here's some simplified sample data:
(the real table has half a million rows)

create table inventory(
   partnumber VARCHAR(10),
   description VARCHAR(10)
);
INSERT INTO inventory (partnumber,description) VALUES
    ('12345','ABCDE'),
    ('123456','ABCDEF'),
    ('1234567','ABCDEFG'),
    ('98765','ZYXWV'),
    ('987654','ZYXWVU'),
    ('9876543','ZYXWVUT'),
    ('abcde',''),
    ('abcdef','123'),
    ('abcdefg','321'),
    ('zyxwv',NULL),
    ('zyxwvu','987'),
    ('zyxwvut','789');

TRIED:
I've tried too many things to list here.
I've finally found a way to get past all the 'unknown field' errors and at least get SOME results, but:

1. it's SUPER kludgy!
2. my results are not limited to unique prods.

Here's my current query:

SELECT
  LEFT(i.partnumber, 6) AS prod,
  CASE
    WHEN agg.cnt > 1
    OR i.description IS NULL
    OR i.description = ''
    THEN LEFT(i.partnumber, 6)
    ELSE i.description
  END AS `descrip`
  FROM inventory i
  INNER JOIN (SELECT LEFT(ii.partnumber, 6) t, COUNT(*) cnt
    FROM inventory ii GROUP BY ii.partnumber) AS agg
  ON LEFT(i.partnumber, 6) = agg.t;

GOAL:
My goal is to retrieve:

QUESTION:

1. What are some cleaner ways to use the COUNT() aggregate data with a CASE type conditional?
2. How can I limit my results so that all prods are UNIQUE?

CodePudding user response：

You can check if a left(partnumber, 6) is not unique in the result by checking if count(*) > 1. In such a case let descrip be left(partnumber, 6). Otherwise you can use max(description) (or min(description)) to get the single description but satisfy the needs to use an aggregation function on columns not in the GROUP BY. To replace empty or NULL descriptions, nullif() and coalesce() can be used.

That would lead to the following using just one level of aggregation and no joins:

SELECT left(partnumber, 6) AS prod,
       CASE
         WHEN count(*) > 1 THEN
           left(partnumber, 6)
         ELSE
           coalesce(nullif(max(description), ''), left(partnumber, 6))
       END AS descrip
       FROM inventory
       GROUP BY left(partnumber, 6)
       ORDER BY left(partnumber, 6);

But there seems to be a bug in MySQL and this query fails. The engine doesn't "see" that, in the list after SELECT partnumber is only used in the expression left(partnumber, 6), which is also in the GROUP BY. Instead the engine falsely complains about partnumber not being in the GROUP BY and not subject to an aggregation function.

As a workaround, we can use a derived table, that does the shortening of partnumber to its first six characters. We then use use that column of the derived table instead of left(partnumber, 6).

SELECT l6pn AS prod,
       CASE
         WHEN count(*) > 1 THEN
           l6pn
         ELSE
           coalesce(nullif(max(description), ''), l6pn)
       END AS descrip
       FROM (SELECT left(partnumber, 6) AS l6pn,
                    description
                    FROM inventory) AS x
       GROUP BY l6pn
       ORDER BY l6pn;

Or we slap some actually pointless max()es around the left(partnumber, 6) other than the first, to work around the bug.

SELECT left(partnumber, 6) AS prod,
       CASE
         WHEN count(*) > 1 THEN
           max(left(partnumber, 6))
         ELSE
           coalesce(nullif(max(description), ''), max(left(partnumber, 6)))
       END AS descrip
       FROM inventory
       GROUP BY left(partnumber, 6)
       ORDER BY left(partnumber, 6);

db<>fiddle (Change the DBMS to some other like Postgres or MariaDB to see that they also accept the first query.)