CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
acadia
acadia

Reputation: 2621

sql Query Issue

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

Upvotes: 0

Views: 98

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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

Upvotes: 5

Steve Kass
Steve Kass

Reputation: 7184

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;

Upvotes: 0

Related Questions