How to get only one data from table if value is same in 2 columns in SQL Server?

user_9402710 0
Viewed 50

I have three tables as below

Table 1 - DevInfo

+----------+--------+-----------+-------------+------+
| Center   | DevId  | DevType   |LatestDate   | DEL  |
+----------+--------+-----------+-------------+------+
| C1       | 111    | T1        | 01/06/2020  | 0    |
| C1       | 222    | T2        | 01/06/2020  | 0    |
| C2       | 111    | T3        | 01/06/2020  | 0    |
| C2       | 333    | T4        | 01/06/2020  | 0    |
| C3       | 222    | T5        | 03/06/2020  | 0    |
| C4       | 444    | T6        | 01/06/2020  | 0    |
+----------+--------+-----------+-------------+------+

Table 2 - CenInfo

+----------+-------------+
| Center   |LatestDate   |
+----------+-------------+
| C1       | 01/06/2020  |
| C1       | 01/06/2020  |
| C2       | 01/06/2020  |
| C2       | 01/06/2020  |
| C3       | 03/06/2020  |
| C4       | 01/06/2020  |
+----------+-------------+

Table 3 - TypeInfo

+-----------+-------------+
| DevType   |LatestDate   |
+-----------+-------------+
| T1        | 01/06/2020  |
| T2        | 01/06/2020  |
| T3        | 01/06/2020  |
| T4        | 01/06/2020  |
| T5        | 03/06/2020  |
| T6        | 01/06/2020  |
+-----------+-------------+

I want to get data from DevInfo table, where

  1. DevInfo table's center is same as in CenInfo table and DevInfo table's Dev Type is same as in TypeInfo table.

  2. If DevId is same(e.g 111) for records then skip the record with DevType = "T3"

  3. If DevId is same for records and DevType is not "T3" then select record with latest "LatestDate" value.

I want output like this:

+----------+--------+-----------+-------------+
| Center   | Dev id | Dev Type  |Latest Date  |
+----------+--------+-----------+-------------+
| C1       | 111    | T1        | 01/06/2020  |
| C2       | 333    | T4        | 01/06/2020  |
| C3       | 222    | T5        | 03/06/2020  |
| C4       | 444    | T6        | 01/06/2020  |
+----------+--------+-----------+-------------+

I get the answer of 1st using an Inner Join, Query as below :

Select
Center, DevId, DevType, LatestDate
From DevInfo DI
INNER JOIN CenInfo CI ON CI.Center = DI.Center
INNER JOIN TypeInfo TI ON TI.DevType = DI.DevType
WHERE DI.DEL = '0'

but didn't get any solution for 2 and 3

Please help me with that.

Thank you in advance.

sql-server
2 Answers

You can use windowed ROW_NUMBER function:

WITH DevInfoFilteredAndSorted AS (
  SELECT 
    DI.Center, 
    DI.DevId, 
    DI.DevType, 
    DI.LatestDate,
    ROW_NUMBER() OVER (PARTITION BY DI.DevId ORDER BY CASE WHEN DI.DevType = 'T3' THEN 1 ELSE 0 END, DI.LatestDate DESC) AS RN
  FROM DevInfo AS DI
  INNER JOIN CenInfo AS CI
    ON DI.Center = CI.Center
  INNER JOIN TypeInfo AS TI
    ON DI.DevType = TI.DevType
  WHERE DI.DEL = 0
)

SELECT DI.Center, DI.DevId AS "Dev id", DI.DevType AS "Dev Type", DI.LatestDate AS "Latest Date"
FROM DevInfoFilteredAndSorted AS DI
WHERE DI.RN = 1

Example on dbfiddle.uk

user_77464520

Use CTE to get RowNumber as per your desired criteria and then perform join like below.

WITH DI AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY DevId ORDER BY IIF(DevType = 'T3', 1, 0), LatestDate DESC) AS RowNumber
    FROM DevInfo
)
SELECT DI.Center, DI.DevId, DI.DevType, DI.LatestDate
From DI
    INNER JOIN CenInfo CI ON CI.Center = DI.Center
    INNER JOIN TypeInfo TI ON TI.DevType = DI.DevType
WHERE DI.RowNumber = 1
user_96952860
Related