No, where conditions as we have other data as well
The following is for PostgreSQL (which you tagged as well), but you could probably find something similar for SQL Server as well.
If you only want the row with
ALL or some other row if that doesn't exist, you can use an order by with an expression and then limit the result to just one row.
select * from the_table where mod_nm = 'COST OUT MODULE' order by case when product_line = 'ALL' then 999 else 1 end fetch first 1 rows only;
This will sort the result based on product_line, and sorts the value
ALL to the end, then only fetches the first row from that result (in SQL Server version before 2012 you would need to use the non-standard
SELECT TOP (1) ... instead).
Assuming SQL Server, you may try with the next approach, which uses
ROW_NUMBER() to number the rows within a partition defined by
mod_nm column. Use
ORDER BY clause with
CASE expression to order rows correctly in each partition and choose an appropriate second order expression (
product_line in the example).
CREATE TABLE #Data ( mod_nm varchar(50), product_line varchar(50) ) INSERT INTO #Data (mod_nm, product_line) VALUES ('COST OUT MODULE', 'WireLine Services'), ('COST OUT MODULE', 'All'), ('OTHER MODULE', 'WireLine Services'), ('OTHER MODULE', 'WireLine'), ('OTHER MODULE', 'Different Product Line')
SELECT mod_nm, product_line FROM ( SELECT mod_nm, product_line, ROW_NUMBER() OVER ( PARTITION BY mod_nm ORDER BY CASE WHEN product_line = 'All' THEN 1 ELSE 0 END, product_line ) AS Rn FROM #Data ) t WHERE Rn = 1
mod_nm product_line COST OUT MODULE WireLine Services OTHER MODULE Different Product Line
Assuming (since your question is incomplete):
This table definition:
CREATE TABLE tbl (tbl_id int, mod_nm text, product_line text)
You want exactly one row per value of
mod_nmand there can be many distinct values.
- Rows with anything but
product_line = 'ALL'come first.
This would do it:
SELECT DISTINCT ON (mod_nm) * FROM tbl ORDER mod_nm, product_line = 'ALL'
product_line = 'ALL' yields
NULL), which sorts in the given sequence in (default) ascending order. Hence, rows with anything but 'All' come first if available. See: