-1

I have a result like the bwlow image. now, i want to select only wireline instead of all. So, how can i select the 1st row if the all columns are same except produtc_line

No, where conditions as we have other data as well

  • 2
    How do you define that wireless is “first” or the one you want? – Aaron Bertrand Jun 3 at 23:25
  • Wireline is not defined at first. it's irregular but I want to take wirline as result if it's compared with ALL – Velaga UmaShankar Jun 3 at 23:30
  • 3
    And if there is only ALL (let's say for a different mod_nm value), do you want that row? If there are two rows that are not ALL, which one do you want? You need to frame your whole problem with examples and edge cases, not just two rows. – Aaron Bertrand Jun 3 at 23:53
1

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).

1

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).

Table:

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')

T-SQL:

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

Output:

mod_nm          product_line
COST OUT MODULE WireLine Services
OTHER MODULE    Different Product Line
0

For Postgres.

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_nm and 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'

The expression product_line = 'ALL' yields FALSE, TRUE (or NULL), which sorts in the given sequence in (default) ascending order. Hence, rows with anything but 'All' come first if available. See:

About DISTINCT ON:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.