Chollasith Seneekatima Chollasith Seneekatima - 5 months ago 24
SQL Question

How to query difference duplicate data

SQL



I have data around

BOMcat | BOM | AltBOM | Counter | ChangeNo | DeID | DID | PreCounter | Baseqty
M | 7882 | A | 26 | GUC320140101 | X | | |100.000
M | 7882 | B | 1 | GUC320140101 | X | | |100.500
M | 7882 | B | 307 | GUC320141225 | X | | 1 |100.500
M | 9772 | 01 | 600 | GUC120140904 | X | | 397 |116.188
M | 9772 | 01 | 601 | GUC120150504 | X | | 600 |116.488
M | 9772 | 01 | 1021 | GUC120150703 | X | | 601 |116.488


i want to select
1 BOM/1 AltBOM
by condition is select last
ChangeNo
.


  • PreCounter
    : Mean this line has change what line EX. PreCounter 1 this line has change
    BOM
    has
    Counter 1

  • 'Changeno': Structrue is GUCXYYYYMMDD

    - X: running number

    - YYYY: Year

    - MM: Month

    - DD: Date



Expect Result



M | 7882 | A | 26 | GUC320140101 | X | | | 100.000
M | 7882 | B | 307 | GUC320141225 | X | | 1 | 100.500
M | 9772 | 01 | 1021 | GUC120150703 | X | | 601 | 116.488

Answer

In MS Access I would do this with a correlated subquery in the where clause:

select t.*
from t
where t.changeno = (select max(t2.changeno)
                    from t t2
                    where t2.bom = t.bom and t2.altbom = t.altbom
                   );