Get rows with a mix of specific values or null if value does not exist-CodePudding

I have a table that looks like this.

id	value	text	type	eqPath	enabled
1	0	Unknown	NULL	NULL	True
2	1	Production	Production	NULL	True
3	2	Idle	Idle	NULL	True
4	3	Maintenance	Maintenance	NULL	True
5	4	Changeover	Changeover	NULL	True
6	5	Cleaning	Cleaning	NULL	True
7	6	Engineering	Other	NULL	True
8	7	Training	Other	NULL	True
9	8	Pause	Idle	NULL	True
10	1	Running	Production	'Seattle/Sorting/Line 1'	True
11	1	Running	Production	'Seattle/Sorting/Line 2'	True
12	5	Washing	Cleaning	'Seattle/Sorting/Line 2'	False
13	10	Locked Out	Maintenance	'Seattle/Sorting/Line 2'	False

When I use this code, I get back all of the rows with an eqPath of NULL.

SELECT *
FROM modes
WHERE eqPath IS NULL

id	value	text	type	eqPath	enabled
1	0	Unknown	NULL	NULL	True
2	1	Production	Production	NULL	True
3	2	Idle	Idle	NULL	True
4	3	Maintenance	Maintenance	NULL	True
5	4	Changeover	Changeover	NULL	True
6	5	Cleaning	Cleaning	NULL	True
7	6	Engineering	Other	NULL	True
8	7	Training	Other	NULL	True
9	8	Pause	Idle	NULL	True

Now I need one that returns the same table, but if the user requests a specific eqPath, then it would also show up in the list, BUT the value column has to be unique with the row containing the eqPath to replace the row with the NULL eqPath.

id	value	text	type	eqPath	enabled
1	0	Unknown	NULL	NULL	True
11	1	Running	Production	'Seattle/Sorting/Line 2'	True
3	2	Idle	Idle	NULL	True
4	3	Maintenance	Maintenance	NULL	True
5	4	Changeover	Changeover	NULL	True
12	5	Washing	Cleaning	'Seattle/Sorting/Line 2'	False
7	6	Engineering	Other	NULL	True
8	7	Training	Other	NULL	True
9	8	Pause	Idle	NULL	True
13	10	Locked Out	Maintenance	'Seattle/Sorting/Line 2'	False

This is the code I am trying, but I realize it is wrong.

SELECT DISTINCT *
FROM modes
WHERE eqPath = ?
OR eqPath IS NULL
GROUP BY value

CodePudding user response：

I think I came up with a solution:

SELECT * 
FROM modes
WHERE eqPath IS NULL
AND value NOT IN (
    SELECT value 
    FROM modes 
    WHERE eqPath = ?
)
UNION
SELECT * 
FROM modes
WHERE eqPath = ?
ORDER BY value

CodePudding user response：

You can use ROW_NUMBER to mark the rows and use it in filter to get the expected result.

A sql query will be something like -

select id, value, text, type, eqPath, enabled
from 
   (select * ,  
      row_number() over( partition by value order by eqPath desc) rn 
   from <your-table>
   where (eqPath is null or eqPath = ?) ) tbl 
where rn = 1 
order by value

the row_number in the query will put a row number for each rows and resets when the value changes, so finally those rows with row number matching 1 will the ones we need to fetch.

here is a fiddle