CODEWITHSUNDEEP
sql-servert-sql
Ganesha
Ganesha

Reputation: 1531

SQL - SELECT rows conditionally

The following is the criteria to select rows:

I came up with this logic 

    DECLARE @Products table
    (
        Id int,
        SysId varchar(100)          
    );

    INSERT INTO @Products
    SELECT Id, SysId FROM ProductMain
    WHERE ProductCode = 'P2'

    INSERT INTO @Products
    SELECT Id, SysId FROM ProductMain WHERE ProductCode = 'P1'
    AND SysId NOT IN (SELECT SysId FROM @subscription)

    --Result
    SELECT Id,SysId FROM @Products

Sample Data

Id  SysId  ProductCode
1   121    P1
2   121    P2
3   122    P1
4   123    P2
5   124    P1
6   124    P2

Desired Output

Id  SysId
2   121
3   122
4   123
6   124

I know there should be a better logic for this. Please help. Thanks in advance.

Upvotes: 1

Views: 143

Answers (3)

Dilip Nannaware
Dilip Nannaware

Reputation: 1480

Considering ProductCodes are assigned values like P1, P2, P3 and you want to get the Id with highest product code. In your case P2 is highest. You can use below given query

SELECT *
FROM
(
    SELECT DENSE_RANK OVER(PARTITION BY SysId ORDER BY ProductCode DESC) As Rank
    FROM @Products
) AS ProductsNew
WHERE Rank = 1

Upvotes: 0

sam
sam

Reputation: 1304

You can try something like this - 

select * from tableA as a
where exists(select 1 from tablea as b where b.sysid = a.sysid and b.productcode = 'P2')  or 
exists(select 1 from tablea as b where b.sysid = a.sysid and b.productcode <> 'P2')

Upvotes: 0

Squirrel
Squirrel

Reputation: 24803

if P1, P2 is not the actual data, change the ORDER BY to CASE WHEN ProductCode = 'P2' THEN 1 ELSE 2 END

SELECT *
FROM
(
   SELECT *, rn = row_number() over (partition by SysId ORDER BY ProductCode DESC)
   FROM   yourtable
) d
WHERE d.rn = 1

Upvotes: 1

Related Questions