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.

[sculptorlv]

Hello Andrew!

Can you please write a little bit more detailed example about using LOAD.

I tried to read manuals, but until now didn't get the idea about using it in my problem.

For now I have database in MS SQL and I joined several tables.

Then I need to remove duplicated rows by Name with only one biggest value from Sign (see first post)

Thank you in advance.

[sculptorlv]

Thank you. I really did a mistake with ".

Now I get the result, but it not removed duplicated rows:

(Document No_ is my "Name" and Applied-to-Scan is my "Sign")

If I remove Applied from Group by, I get mistake:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Column 'EUR-VENDEN.dbo.EUR VENDEN$Document Rows.Applied-to Scan Result' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[maxgro · Accepted solution]

for a group by to works

select

     field1, field2,

     sum(field3), count(field4), avg(field5), .......

from table

where ...

group by field1, field2;

the fields without sum or count or ..... must be in the group by

you have field1 and field2 in the select list

you don't use an aggregation function (sum ,....) for field1 and field2

so field1 and field2 must be in the group by

[sculptorlv]

Thank you! I finally get the result I needed.

Can share code for other, if someone will read this topic.

SQL SELECT

Tbl_DocumentRows."Document No_" AS Document_Number,

MAX(Tbl_DocumentRows."Applied-to Scan Result") AS Applied,

Tbl_DocumentHeaders."New FA Location Code" AS IN_Location,

Tbl_DocumentHeaders."FA Location Code" AS OUT_Location,

DAY(Tbl_DocumentHeaders."Posting Date") AS Day,

MONTH(Tbl_DocumentHeaders."Posting Date") AS Month,

YEAR(Tbl_DocumentHeaders."Posting Date") AS Year,

Tbl_FACLASS.Code AS FA_cLASS,

(CASE Tbl_DocumentHeaders."Doc_ Type"

  WHEN '1'  THEN 'Uz kontraktu'

  WHEN '2'  THEN 'Uz degustaciju'

  WHEN '3'  THEN 'No kontrakta'

  WHEN '4'  THEN 'No degustacijas'

  WHEN '5'  THEN 'Profilakse'

  WHEN '6'  THEN 'Pardosana'

  WHEN '7'  THEN 'Nonemt no uzskaites'

  WHEN '8'  THEN 'Ieksejais parvietojums'

  WHEN '9'  THEN 'Uz ekspluataciju'

  WHEN '10' THEN 'Noma' END) AS Document_Type,

(CASE Tbl_DocumentHeaders."Doc_ Reason"

  WHEN '1'  THEN 'Jauns klients'

  WHEN '2'  THEN 'Papildus iekarta'

  WHEN '3'  THEN 'Parregistracija'

  WHEN '4'  THEN 'Klases maina'

  WHEN '5'  THEN 'No degustacijas uz ligumu'

  WHEN '6'  THEN 'Udens un pudeles'

  WHEN '7'  THEN 'Uz pasakuma periodu'

  WHEN '8'  THEN 'Iekartas samazinajums'

  WHEN '9'  THEN 'Beidzas kontrakts'

  WHEN '10' THEN 'Beidzas degustacija'

  WHEN '11' THEN 'Pec pasakuma laika'

  WHEN '12' THEN 'Profilakse'

  WHEN '13' THEN 'Pardosana'

  WHEN '14' THEN 'Nonemts no uzskaites'

  WHEN '15' THEN 'Ieksejaias parvietojums'

  WHEN '16' THEN 'Uz ekspluataciju'

  WHEN '17' THEN 'Noma'

  WHEN '18' THEN 'Rent' END) AS Document_Reason,

(CASE Tbl_DocumentHeaders.Status2

  WHEN '1' THEN 'Atverts'

  WHEN '2' THEN 'Aprekinats'

  WHEN '3' THEN 'Nodots'

  WHEN '4' THEN 'Iegramatots' END) AS Status,

Tbl_DocumentHeaders."Route ID" AS Route,

Tbl_DocumentHeaders."Posting Date" AS Posting_Date,

Tbl_Fixed_Assets."FA Barcode" AS Barcode,

Tbl_Fixed_Assets."Blocked" AS Carantine

//FROM and JOIN

FROM "EUR-VENDEN".dbo."EUR VENDEN$Document Rows" AS Tbl_DocumentRows

//Join connect to Table Clases in Navision

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$FA Class" AS Tbl_FACLASS

ON Tbl_DocumentRows."FA Class Code" = Tbl_FACLASS.Code

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$Document Headers" AS Tbl_DocumentHeaders

ON Tbl_DocumentHeaders."Document No_" = Tbl_DocumentRows."Document No_"

LEFT JOIN "EUR-VENDEN".dbo."EUR VENDEN$Fixed Asset" AS Tbl_Fixed_Assets

ON Tbl_DocumentRows."No_" = Tbl_Fixed_Assets."No_"

WHERE

Tbl_DocumentHeaders."Posting Date" > '2015-11-30 00:00:00.000'

AND

Tbl_FACLASS."Act FA No_ fill type" = 1

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 14

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 16

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 15

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 5

AND

Tbl_DocumentHeaders."Doc_ Reason" <> 3

AND

Tbl_DocumentHeaders.Status2 = 4

GROUP BY

Tbl_DocumentRows."Document No_",

Tbl_DocumentHeaders."New FA Location Code",

Tbl_DocumentHeaders."FA Location Code",

Tbl_DocumentHeaders."Posting Date",

Tbl_FACLASS.Code,

Tbl_DocumentHeaders."Doc_ Type",

Tbl_DocumentHeaders."Doc_ Reason",

Tbl_DocumentHeaders.Status2,

Tbl_DocumentHeaders."Route ID",

Tbl_Fixed_Assets."FA Barcode",

Tbl_Fixed_Assets."Blocked"

;