Return One Of Two Duplicate Rows Based on Column Value

[larrydavid]

Hello,

Currently I have output like this:
[PRODUCT] [DATE] [ORDER] [FLAG]
TESTNAME 2008-05-30 123456 VALUE_ABC
TESTNAME 2008-05-30 123456 VALUE_XYZ

And need to return one row, but only the one with VALUE_XYZ.

I was trying a UNION of sorts but no luck, researched a bit but no luck. This will be transferred to DB2, so unfortunately I don't have the luxury of being able to write a user defined function or stored proc. I need to write this inline as a query.

Any help would be greatly appreciated.

Thanks,
Larry

 

[larrydavid]

Hello again,

Sorry, I should have mentioned that the results are mixed with other rows, so the output is currently:

[PRODUCT] [DATE]     [ORDER] [FLAG]
TESTNAME1 2008-05-30 354565  VALUE_123
TESTNAME  2008-05-30 123456  VALUE_ABC 
TESTNAME  2008-05-30 123456  VALUE_XYZ 
TESTNAME2 2008-05-30 546576  VALUE_345 
TESTNAME3 2008-05-30 232453  VALUE_678

The desired result is

[PRODUCT] [DATE]     [ORDER] [FLAG]
TESTNAME1 2008-05-30 354565  VALUE_123
TESTNAME  2008-05-30 123456  VALUE_XYZ 
TESTNAME2 2008-05-30 546576  VALUE_345 
TESTNAME3 2008-05-30 232453  VALUE_678

Hope this makes sense.

Thanks,
Larry

 

[bborissov]

How do you choose which record you want in the result?
What is the PK of that table?

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

You're not making the rules you want applied very clear. Eg `the flag VALUE_XYZ is only in one of the original rows, so what is the general rule? The record with highest flag value?
Without knowing the rules of selection it's impossible to help you.

Bye, Olaf.

 

[DaveInIowa]

Would it be as simple as this?

SELECT [PRODUCT], [DATE], [ORDER], MAX([FLAG]) FLAG
  FROM SomeTable
 GROUP BY [PRODUCT], [DATE], [ORDER]

 

[larrydavid]

Sorry, I thought it was pretty clear, If there are two rows with TESTNAME, one will have a FLAG value of VALUE_XYZ, the other VALUE_ABC. If these two appear in a result, remove the one with VALUE_ABC. Otherwise, if the one with VALUE_ABC only exists, just display that one. If the one with VALUE_XYZ already exists, display that one. Hope this makes sense.
Thanks, Larry

 

[SkipVought]

Where [flag]<>'VALUE-ABC'

 

[Olaf Doschke]

Well, no it's not clear, as there are far more different values than the two.

MAX(FLAG) is perhaps the only thing you need, if it's the only varying field aside of the grouping by product, date and order number. So did you try Dave's query?

Bye, Olaf.

 

[djj55]

From the explanation I would go with Dave's.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

I have one more question,
what IF you have:
[tt]
[PRODUCT] [DATE] [ORDER] [FLAG]
TESTNAME 2008-05-30 123456 VALUE_123
TESTNAME 2008-05-30 123456 VALUE_ABC
TESTNAME 2008-05-30 123456 VALUE_XYZ
TESTNAME 2008-05-30 123456 VALUE_344
[/tt]
which one?


Borislav Borissov
VFP9 SP2, SQL Server

 

[DaveInIowa]

Piggy-backing on Borislav's question, what if you have multiple VALUE_XYZ rows? Return one or all?

[PRODUCT] [DATE] [ORDER] [FLAG]
TESTNAME 2008-05-30 123456 VALUE_123
TESTNAME 2008-05-30 123456 VALUE_ABC
TESTNAME 2008-05-30 123456 [red]VALUE_XYZ[/red]
TESTNAME 2008-05-30 123456 [red]VALUE_XYZ[/red]
TESTNAME 2008-05-30 123456 VALUE_344