sql Query Issue

Question

Hello experts,

I have a table as shown below

Q_ID  DeptID  EmployeeName   City
100   100       testest      abcd
100   101         tata        cdd

with 100K records. I need a query which should fetch all records with same Q_ID but different DEPTID.

Please help.

Thanks

Answer 1

Join it to itself:

SELECT t1.*, t2.DeptID
FROM [MyTable] t1
INNER JOIN [MyTable] t2 ON t2.Q_ID=t1.Q_ID AND t2.DeptID>t1.DeptID

Answer 2

You can also do this with analytic functions and thus avoid a join. In more situations than not, this will be more efficient, but it depends on the actual data and indexing.

with TRanked as (
  select
    QID,
    DeptID,
    EmployeeName,
    City,
    dense_rank() over (
      partition by CustomerID
      order by EmployeeID
    ) as ct
  from T
), TMaxRk as (
  select
    QID,
    DeptID,
    EmployeeName,
    City,
    max(ct) over (partition by CustomerID) as maxRk
  from TRanked
)
  select
    QID,
    DeptID,
    EmployeeName,
    City
  from TMaxRk
  where maxRk > 1;