内部联接的重复记录

Question

I'm migrating a project that was pulling data from a MySQL database to a SQL database. The problem is that i'm running into duplicate records that I was able to quickly resolve when I was using a MySQL database. SQL on the other hand is giving me issues.

I have two tables: AllOrderData and CallSettings

Both tables have a column labeled OrderNumber. Sometimes there are multiple records on both tables with the same OrderNumber. I only need to display one record in these situations.

This is what i have now:

$dt = $_GET ["dt"];

$sql = "SELECT
  A.OrderNumber,
  A.DetailShipDate,
  A.ETC,
    C.OrderNumber,
    C.ETC
FROM AllOrderData A
INNER JOIN CallSettings C ON A.OrderNumber = C.OrderNumber
WHERE A.DetailShipDate = '$dt'";

I tried to add DISTINCT and also GROUP BY but I've read they're used for distinct rows and it's just not working.

When I was using MySQL, I had all the data in one table. Unfortunately, that's not an option anymore but the query was very simple:

$sql_string = "SELECT * FROM orders WHERE DetailShipDate='$dt' group by Order_Number

I tried the following:

 $sql = "SELECT x.OrderNumber,
                x.EnteredBy,
                x.Order_Number,
                x.Contact_Name
   FROM (SELECT A.OrderNumber,
                A.EnteredBy,
                C.Order_Number,
                C.Contact_Name
                row_number() OVER (PARTITION BY A.OrderNumber
                                   ORDER BY C.Order_Number) rn
                FROM AllOrderData A
                     INNER JOIN CallSettings C
                                ON A.OrderNumber = C.Order_Number
                WHERE A.DetailShipDate = '$dt') x
   WHERE x.rn = 1;";

And Got this error:

Error in query preparation/execution. Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '('. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '('. ) )

Answer 1

You can use row_number(). Partition and order by the order number.

SELECT x.ordernumber,
       x.detailshipdate,
       x.etc,
       x.ordernumber,
       x.etc
       FROM (SELECT a.ordernumber,
                    a.detailshipdate,
                    a.etc,
                    c.ordernumber,
                    c.etc,
                    row_number() OVER (PARTITION BY c.ordernumber
                                       ORDER BY c.ordernumber) rn
                    FROM allorderdata a
                         INNER JOIN callsettings c
                                    ON a.ordernumber = c.ordernumber
                    WHERE a.detailshipdate = ?) x
       WHERE x.rn = 1;