Selecting Only Certain Rows In A Group

Question

Selecting Only Certain Rows In A Group

thomas_w_rawley

SSC-Addicted

Points: 422
More actions
March 16, 2009 at 1:46 pm

#131830

Hello. I am having trouble wrapping my head around this one. The data set I am working with follows (same data model...made up data).
SHIPMENT STOP INPUT OUTPUT
1 1 DROP LIVE
1 2 DROP LIVE
1 3 DROP DROP
2 1 LIVE LIVE
2 2 LIVE LIVE
3 1 DROP LIVE
3 2 DROP DROP
Let me explain the about table some. Shipments 1 and 3 are what I call drop shipments. A drop shipment means all stops on the shipment are live EXCEPT the last one which is drop. The drop/live indicator is stored at the shipment level though...not the stop level. Shipment 2 is live. I will filter out live shipments so we don't need to worry about them...I just included Shipment 2 for completeness.
I have columns SHIPMENT, STOP, DROP_OR_LIVE in a table (where DROP_OR_LIVE has the values of INPUT column above). This is what I am given. What I need to convert it to via an UPDATE statement so that the values in DROP_OR_LIVE will look like those in the OUTPUT column above.
I would prefer not to iterate over all unique SHIPMENT values in a for loop or something as this seems dirty to me. The logic is easy for one shipment (just update all but the last stop) but seems harder for multiple shipments.
Any suggestions are greatly appreciated.
Thanks,
Thomas

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 1

It is not easy to understand what you are trying to do without examples. Please review the article that I link to in my signature on how to post questions to get better and faster answers.

Once you have reviewed that article, post the create table statements, insert statements and expected results - and someone here will be able to answer your question very easily.

Also, you should post what you have attempted so far - that would at least give someone a place to start.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

Does this query do what you need?

It will only update rows for those shipments where the Drop_Or_Live column values are all 'DROP'.

CREATE TABLE #DropShipment (

Shipment int NOT NULL,

Stop int NOT NULL,

Drop_Or_Live char(4) NOT NULL

)

/* Test data */

INSERT #DropShipment (Shipment, Stop, Drop_Or_Live)

SELECT 1, 1, 'DROP' UNION ALL

SELECT 1, 2, 'DROP' UNION ALL

SELECT 1, 3, 'DROP' UNION ALL

SELECT 2, 1, 'LIVE' UNION ALL

SELECT 2, 2, 'LIVE' UNION ALL

SELECT 3, 1, 'DROP' UNION ALL

SELECT 3, 2, 'DROP'

UPDATE D SET Drop_Or_Live = 'LIVE'

FROM #DropShipment D

INNER JOIN (

SELECT Shipment, MAX(Stop) AS Stop

FROM #DropShipment

GROUP BY Shipment

HAVING (MIN(Drop_Or_Live) = 'DROP') AND (MAX(Drop_Or_Live) = 'DROP')

) A ON (D.Shipment = A.Shipment AND D.Stop < A.Stop)

EDIT: Changed column name to Drop_Or_Live

thomas_w_rawley SSC-Addicted Points: 422 More actions · Answer 3

That is EXACTLY what I need. I changed it to this which makes a little more sense in my head...and seems to provide the same result.

UPDATE D SET Drop_Or_Live = 'LIVE'

FROM DROP_TEST D

INNER JOIN (

SELECT Shipment, MAX(Stop) AS Stop

FROM DROP_TEST

WHERE Drop_or_Live = 'DROP'

GROUP BY Shipment) A

ON (D.Shipment = A.Shipment AND D.Stop < A.Stop)

EDIT: I see where you had the logic to include only shipments where every stop was DROP...my source data will always have that so that is why I added the WHERE clause...but good idea for the future.

Thanks!

Thomas