CODEWITHSUNDEEP
sql-server
Chase Ernst
Chase Ernst

Reputation: 1155

SQL Get Second Record

I am looking to retrieve only the second (duplicate) record from a data set. For example in the following picture:

enter image description here

Inside the UnitID column there is two separate records for 105. I only want the returned data set to return the second 105 record. Additionally, I want this query to return the second record for all duplicates, not just 105.

I have tried everything I can think of, albeit I am not that experience, and I cannot figure it out. Any help would be greatly appreciated.

Upvotes: 1

Views: 2292

Answers (3)

ErikE
ErikE

Reputation: 50201

Assuming SQL Server 2005 and up, you can use the Row_Number windowing function:

WITH DupeCalc AS (
   SELECT
      DupID = Row_Number() OVER (PARTITION BY UnitID, ORDER BY JobUnitKeyID),
      *
   FROM JobUnits
   WHERE DispatchDate = '20151004'
   ORDER BY UnitID Desc
)
SELECT *
FROM DupeCalc
WHERE DupID >= 2
;

This is better than a solution that uses Max(JobUnitKeyID) for multiple reasons:

  • There could be more than one duplicate, in which case using Min(JobUnitKeyID) in conjunction with UnitID to join back on the UnitID where the JobUnitKeyID <> MinJobUnitKeyID` is required.
  • Except, using Min or Max requires you to join back to the same data (which will be inherently slower).
  • If the ordering key you use turns out to be non-unique, you won't be able to pull the right number of rows with either one.
  • If the ordering key consists of multiple columns, the query using Min or Max explodes in complexity.

Upvotes: 1

Chesser
Chesser

Reputation: 465

Use RANK function. Rank the rows OVER PARTITION BY UnitId and pick the rows with rank 2 .

For reference -

https://msdn.microsoft.com/en-IN/library/ms176102.aspx

Upvotes: 1

Morgan Thrapp
Morgan Thrapp

Reputation: 9986

You need to use GROUP BY for this.

Here's an example: (I can't read your first column name, so I'm calling it JobUnitK

SELECT MAX(JobUnitK), Unit
FROM JobUnits
WHERE DispatchDate = 'oct 4, 2015'
GROUP BY Unit  
HAVING COUNT(*) > 1

I'm assuming JobUnitK is your ordering/id field. If it's not, just replace MAX(JobUnitK) with MAX(FieldIOrderWith).

Upvotes: 1

Related Questions