Getting rid of not needed values in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting rid of not needed values in a column

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-09-15 : 17:56:53

I have a table that looks like this:

IME_ID IME_EQUIP_IN_ID IME_EQUIP_IN_DEMAND_ID IME_EQPT_ENTRY_ID HOST_NAME QUANTITY WO_NO WO_STATUS
00080961 000000000374999 000000000776183 00251526 boldb2 920 0001783434 Completed
00080961 000000000374999 000000000776183 00251526 boldb2 920 0001783517 Completed
00080961 000000000375002 000000000776185 00251529 boldb1 540 0001783435 Completed
00080961 000000000375002 000000000776185 00251529 boldb1 540 0001783518 Completed

I am trying to get rid off too many work orders ( see WO_NO column), after successful cleanup. In this sample, I would like to see just 2 rows with these 2 WO_NOs:
0001783517
0001783518

How shoudl I accomplish this? Please note I have more columns, just showing several for illustration

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 18:26:18

Either of the following should work:


-- 1
SELECT
	b.*
FROM
	YourTable a
	CROSS APPLY
	(
		SELECT TOP (1) *
		FROM YourTable b
		WHERE 
			b.IME_EQUIP_IN_ID = a.IME_EQUIP_IN_ID
			AND b.IME_EQUIP_IN_DEMAND_ID = a.IME_EQUIP_IN_DEMAND_ID
			-- and whatever other conditions you need to add
		ORDER BY
			WO_NO DESC
	)b;

-- 2
SELECT *
FROM
(
	SELECT *,
		ROW_NUMBER() OVER 
		(
			PARTITION BY IME_EQUIP_IN_ID,IME_EQUIP_IN_DEMAND_ID -- and other columns
			ORDER BY WO_NO DESC
		) AS RN
	FROM
		YourTable
)s
WHERE RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-15 : 21:55:30

[code]
SELECT t.*
FROM Table t
INNER JOIN
(
SELECT IME_ID, IME_EQUIP_IN_ID, IME_EQUIP_IN_DEMAND_ID, IME_EQPT_ENTRY_ID, HOST_NAME, QUANTITY,MAX(WO_NO) AS latest
FROM Table
GROUP BY IME_ID, IME_EQUIP_IN_ID, IME_EQUIP_IN_DEMAND_ID, IME_EQPT_ENTRY_ID, HOST_NAME, QUANTITY
)t1
ON t1.IME_ID = t.IME_ID
AND t1.IME_EQUIP_IN_ID = t.IME_EQUIP_IN_ID
AND t1.IME_EQUIP_IN_DEMAND_ID = t.IME_EQUIP_IN_DEMAND_ID
AND t1.IME_EQPT_ENTRY_ID = t.IME_EQPT_ENTRY_ID
AND t1.HOST_NAME = t.HOST_NAME
AND t1.QUANTITY = t.QUANTITY
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-09-17 : 09:10:32

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources