Selecting Rows with Minimum Value

Question

I am struggling to complete a query in which I need to get only those rows that have a "minimum value" in a specific field. This doesn't quite fit into my understanding of using MIN() and I'm hoping someone can point me in the right direction.

I have a query that joins two tables and orders the result values by a sort_order.

CREATE TEMPORARY TABLE_TEMP_1
(
   RU_LVL      VARCHAR(3) NOT NULL,
   SORT_ORDER  SMALLINT NOT NULL
);

INSERT INTO TABLE_TEMP_1 (RU_LVL, SORT_ORDER) VALUES ('50', 1);
INSERT INTO TABLE_TEMP_1 (RU_LVL, SORT_ORDER) VALUES ('60', 2);
INSERT INTO TABLE_TEMP_1 (RU_LVL, SORT_ORDER) VALUES ('70', 3);
INSERT INTO TABLE_TEMP_1 (RU_LVL, SORT_ORDER) VALUES ('40', 4);
INSERT INTO TABLE_TEMP_1 (RU_LVL, SORT_ORDER) VALUES ('35', 5);

SELECT *
FROM R_EXPORT_RU R_EXPORT_RU
INNER JOIN TABLE_TEMP_1 TT ON TT.RU_LVL = R_EXPORT_RU.RU_LVL_ID
WHERE PER_ID = 12345
ORDER BY TT.SORT_ORDER;

You can see from that query that I am trying to introduce a custom sort order for a character field, the RU_LVL.

This query works fine to bring all the data together and it is properly sorted by SORT_ORDER. However, I really only want to get the rows with the lowest SORT_ORDER value. For some people, that value might be 1, for others, it might be 4.

My initial thought was to sort the data and then grab the first row but, unfortunately, there are a couple rows in the R_EXPORT_RU table that share the same RU_LVL_ID value. Because of that, it's possible to get multiple rows that have the same minimum sort order, like this:

+----------------------------------------------------+
| PER_ID | RU_DESC | RU_LVL_ID | RU_LVL | SORT_ORDER |
+----------------------------------------------------+
| 12345  | Foo     | 40        | 40     | 4          |
| 12345  | Bar     | 40        | 40     | 4          |
| 12345  | Baz     | 35        | 35     | 5          |
+----------------------------------------------------+

In this case, I want the top two rows. Unfortunately, I don't know ahead of time what the minimum value in SORT_ORDER will be and I don't know how many rows might match that minimum. Because of that, I'm struggling to know how to use MIN() or FETCH FIRST functions to limit results.

Can anyone point me in the right direction?

Thanks!

Try this: `SELECT A.* FROM R_EXPORT_RU A JOIN (SELECT PER_ID, MIN (SORT_ORDER) SORT_ORDER FROM R_EXPORT_RU GROUP BY PER_ID) G ON (G.PER_ID, G.SORT_ORDER) = (A.PER_ID, A.SORT_ORDER)` — Mark Barinstein, Jun 27 '22 at 18:12

score 1 · Answer 1 · answered Jun 27 '22 at 18:17

I'm not familiar with DB2, but I just answered a similar question about 4 minutes ago, so this is easy for me to copy/paste.

Using a SQL Generator I found that using QUALIFY was the recommendation, but I don't think that works in DB2.

To do this without QUALIFY, you might try something like this:

SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY PER_ID, RU_LVL_ID ORDER BY SORT_ORDER ASC) AS RN
    FROM table
    ) AS T
WHERE T.RN = 1;

For RDBMS that support QUALIFY,

SELECT 
  * 
FROM 
  table QUALIFY ROW_NUMBER() OVER (
    PARTITION BY PER_ID, RU_LVL_ID 
    ORDER BY SORT_ORDER ASC
  ) = 1

Selecting Rows with Minimum Value

1 Answers1