SELECT - from duplicate rows under condition

[sculptorlv]

Hello, I hope for your help.

I have table with following columns (and values in rows):

ID     Name      Sign

1       ABC          0

2        DD          0

3        CC          1

4        ABC          1

5          DD          0

I need such select to get the same columns, but another rows:

- only 1 row with the same "Name" (name should be unique)

- if all rows with the same "Name" have value 0,  then the first row only

- if rows with the same "Name" have value 1, then the first row only

-if rows with the same "Name" have 1 and 0, then first row with value 1

The result shold be the following:

ID     Name      Sign

2        DD          0

3        CC          1

4        ABC          1

Thanks for your help in advance.

[ecolomer]

Is this?

[maxgro]

source:

load * inline [

ID     Name      Sign

1       ABC          0

2        DD          0

3        CC          1

4        ABC          1

5          DD          0

] (delimiter is spaces);

final:

NoConcatenate load *

Resident source

Where Peek('Name') <> Name

Order By Name, Sign desc;

DROP Table source;

[effinty2112]

Hi Ruslans,

                    Here's another way:

Data:

LOAD * INLINE [

    ID,     Name,      Sign

    1,       ABC,          0

    2,        DD,         0

    3,        CC,          1

    4,        ABC,          1

    5,          DD,          0

];

Inner Keep(Data)

AggrData:

LOAD

Name,

Max(Sign) as Sign

Resident Data Group by Name;

Drop Table AggrData;

Inner Keep(Data)

Result:

LOAD

Min(ID) as ID,   

Name,    

FirstValue(Sign) as Sign

Resident Data Group by Name;

Drop Table Result;

Suggest you use maxgro's suggestion which is much better.

[sculptorlv]

Thank you all for answers.

I didn't mentioned 

I don't use LOAD, but SELECT from regular data base table.

Thus, I have a lot of columns and rows .. and I show only one simple example in order to get general idea, how to solve my problem.

[maxgro]

maybe

select a.ID, a.Name, a.Sign from

(select name, sign, min(ID) as ID from Table_2 group by name, sign) a

inner join

(select Name, max(Sign) as Sign from Table_2 group by Name) b

on a.Name = b.Name and a.Sign = b.sign

order by a.ID

table top, query result bottom

[sculptorlv]

Thank you!

This thing work, but I use many difficult equations. Thus I have to get rid of multiple rows on SQL level

[sculptorlv]

I got syntax error as soon as I put MAX() ...

[effinty2112]

Hi Ruslans,

You could consider a preceding load in your script. It may be a matter of taste I like to keep SELECT statements as simple as possible. It often makes sense to put WHERE clauses here but other than that I prefer to do any renaming, aggregating etc in the preceding load.

Whatever way you go I hope you get a solution.

Cheers

[maxgro]

Applied-to Scan Result"

you have a " only on the right

and I think also you have to group by only by "Document No_"