how to filter duplicate rows with different value

Question

I hv a table like this

Model     Qty        Date
ABC        1         20110102
ABC       -1         20110105
QWE        1         20110103
ZXC        1         20110103
ABC        1         20110110
QWE       -1         20110110

I wish to hv the final output like this:-

Model     Qty        Date
ZXC        1         20110103
ABC        1         20110110

How can I do this via SQL ?

Thanks

Please explain the logic that takes you from the original table to the result. We are not mind readers. — Oded
– Oded, Commented Jan 22, 2011 at 9:30

eumiro · Accepted Answer · 2011-01-22 09:31:05Z

4

You want the sum of the Qty and the latest Date for each Model:

SELECT Model, SUM(Qty), MAX(Date)
FROM YourTable
GROUP BY Model
HAVING SUM(Qty) <> 0

QWE has a sum of Qty = 0 and therefore should not be returned, right?

answered Jan 22, 2011 at 9:31

eumiro

214k36 gold badges307 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lweller · Accepted Answer · 2011-01-22 09:31:35Z

0

select t1.Model, t1.Qty, t1.Date from thetable t1 where t1.Date = (select max(t2.Date) from thetable t2 where t2.Model=t1.Model)

answered Jan 22, 2011 at 9:31

lweller

11.3k3 gold badges37 silver badges39 bronze badges

1 Comment

lweller Over a year ago

this selects the record with the newest date

Collectives™ on Stack Overflow

how to filter duplicate rows with different value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related