cancel
Showing results for 
Search instead for 
Did you mean: 

Query issue - selecting the last customer equipment card for a serial item

Former Member
0 Kudos

I am trying to select the last (highest numbered or latest) customer equipment card for each of our serialized items.  My query picks up all cards, not just the highest numbered one.

SELECT

  T0.[insID],

  T0.[manufSN],

  T0.[custmrName],

  T0.[U_Last_Rev_Dt],

  T0.[itemCode],

  T0.[status]

FROM OINS T0

Where   T0.[insID] in

  (Select MAX (T1.[insID])

   From dbo.OINS T1

   Where T0.insID = T1.insID)

-- Group by T0.[manufSN])

ORDER BY T0.[manufSN], T0.[insID]

tbanks@avinger.com

Accepted Solutions (1)

Accepted Solutions (1)

former_member212181
Active Contributor
0 Kudos

HI Tony,

Please try below query and give your feedback

---Query Starts

; WITH LastEquip As

(

SELECT T0.[itemCode],Max(T0.insID)LastCard

FROM OINS T0

Group By T0.ItemCode

)

SELECT

  T0.[insID],

  T0.[manufSN],

  T0.[custmrName],

-- T0.[U_Last_Rev_Dt],

  T0.[itemCode],

  T0.[status]

FROM OINS T0

  Inner Join LastEquip T1 on T0.ItemCode = T1.ItemCode and T0.insID =T1.LastCard

ORDER BY T0.[manufSN], T0.[insID]

----Query Ends

Warm Regards

Unnikrishnan

Answers (2)

Answers (2)

former_member212181
Active Contributor
0 Kudos

Hi Tony,

You can try below query also

Select * from

(

SELECT ROW_NUMBER() OVER(PARTITION BY T0.ItemCode ORDER BY T0.insID desc ) AS Row,

T0.[insID], T0.[manufSN], T0.[custmrName]

--, T0.[U_Last_Rev_Dt]

, T0.[itemCode], T0.[status]

FROM OINS T0

) A

Where A.Row=1

ORDER BY A.manufSN, A.[insID]

Thanks

Unnikrishnan

Former Member
0 Kudos

I made a slight modification to your first query and got it to work, selecting the last equipment card for each serial number.  I switched the T0.[ItemCode] with T0.[ManufSN], commented out the T0.[ItemCode] join and it worked!   Thanks Unnikrishnan!   I couldn’t have done it without you.

former_member212181
Active Contributor
0 Kudos

Hi Tony,

Nice to hear that you made it.

If you got the answer, please close the thread by marking correct / helpful answer.

Regards

Unnikrishnan

frank_wang6
Active Contributor
0 Kudos

you should use TOP 1 in your query with "Order BY XXX DESC"

You dont need to use MAX.