CODEWITHSUNDEEP
sqlsql-server
DJ_TecRoot
DJ_TecRoot

Reputation: 93

Selecting only one row if the same ID - SQL Server

I'm trying to learn SQL commands and working currently with an query which will list all customers which has status active (ID = 1) and active-busy (ID = 2).

The problem is that some customers have the same ID but the different type. So I have an customer which has ID 1 and Type 3 but the same customer has also ID 1 but Type 1 so what I'm trying to do is select only this which has Type 1 but have also the same ID. So IF ID is the same and Type is 1 and 3, select only Type 3. 

SELECT 
    CASE 
       WHEN corel.opts LIKE 3
          THEN (SELECT corel.opts
                WHERE corel.objid = rel.id
                  AND corel.type IN (1, 2)
                  AND corel.opts = 3 
          ELSE corel.opts 1 
    END)

It's not complete query because it has many other this which I can't post but if you guys would show me way how could I accomplish that, I would appreciate it. I just don't know how to tell IF the same ID in the table but different Type - select only Type 3. Each customer have different ID but it can have the same type.

Upvotes: 1

Views: 219

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Test scenario is borrowed form Jayasurya Satheesh, thx, voted your's up!

DECLARE @T TABLE
(
    Id INT,
    TypeNo INT
)

INSERT INTO @T
VALUES(1,1),(1,3),(2,1),(2,3),(3,1),(4,3)

--The query will use ROW_NUMBER with PARTITION BY to start a row count for each T.Id separately. --SELECT TOP 1 WITH TIES will take all first place rows and not just the very first:

SELECT TOP 1 WITH TIES
    Id,
    TypeNo
FROM @T AS T
ORDER BY ROW_NUMBER() OVER(PARTITION BY T.Id ORDER BY T.TypeNo DESC)

If your Type=3 is not the highest type code the simple ORDER BY T.TypeNo DESC won't be enough, but you can easily use a CASE to solve this.

Upvotes: 1

Anton Tupy
Anton Tupy

Reputation: 969

As far as I understand, you need something like:

SELECT c1.* 
FROM corel c1
LEFT OUTER JOIN corel c2 ON c1.objid=c2.objid AND c1.type <> c2.type
WHERE (c1.type=1 AND c2.type IS NULL) OR (c1.type=3 AND c2.type=1)

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

USE Row_number() like this

DECLARE @T TABLE
(
    Id INT,
    TypeNo INT
)

INSERT INTO @T
VALUES(1,1),(1,3),(2,1),(2,3),(3,1),(4,3)

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY TypeNo DESC),
        Id,
        TypeNo
        FROM @T
)
SELECT
    Id,
    TypeNo
    FROM CTE
        WHERE RN = 1

My Input

enter image description here

Output

enter image description here

Upvotes: 1

Related Questions