DECLARE @tab TABLE(WorkRequest VARCHAR(10), WorkSegment VARCHAR(20), ChildSegment int, Status VARCHAR(10), Equipment VARCHAR(15))
INSERT INTO @tab
SELECT 'IAM2001', 'FK19929100101', 311, 'Complete', '001VALID' union all
SELECT 'IAM2001', 'FK19929120501', 111, 'Pending', '005VERBO' union all
SELECT 'IAM2001', 'FK19929120501', 121, 'Pending', '005VERBO' union all
SELECT 'IAM2001', 'FK19929141001', 111, 'Pending', '010CONTU' union all
SELECT 'IAM2001', 'FK19929141001', 121, 'Pending', '010CONTU' union all
SELECT 'IAM2002', 'FK19929100101', 311, 'Complete', '001VALID' union all
SELECT 'IAM2002', 'FK19929120501', 111, 'Complete', '005VERBO' union all
SELECT 'IAM2002', 'FK19929120501', 121, 'Complete', '005VERBO' union all
SELECT 'IAM2002', 'FK19929141001', 111, 'Pending', '010CONTU' union all
SELECT 'IAM2002', 'FK19929141001', 121, 'Pending', '010CONTU'

--I need to get the below two records
SELECT WorkRequest, WorkSegment, ChildSegment, Status, Equipment
FROM (SELECT *, ROW_NUMBER() OVER( PARTITION BY WorkRequest ORDER BY (select 1)) RN
		FROM @tab
		WHERE Status = 'pending'
	 ) t
WHERE RN=1

quote:

---

Originally posted by kyadav

Thank you

---