on 06-15-2015 5:25 PM
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]
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
you should use TOP 1 in your query with "Order BY XXX DESC"
You dont need to use MAX.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.