SQL Server return distinct rows?-CodePudding

I'm running this SQL statement:

SELECT   
    s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice, 
    S.salestax, S.linetotal, S.salenbr, C.company, P.MOP
FROM            
    sales S
JOIN
    cust C ON S.custid = C.custid
JOIN
    pmts P ON S.salenbr = p.salenbr
WHERE        
    (s.salenbr = 16749)

It's returning this result set:

indx    custid  date    qty item    price   extprice    salestax    linetotal   salenbr company MOP
170835  695 2021-09-27 10:00:44.000 1.00    1X4X12  7.85    7.85    0.75    8.60    16749   COUNTER SALE    CS   
170835  695 2021-09-27 10:00:44.000 1.00    1X4X12  7.85    7.85    0.75    8.60    16749   COUNTER SALE    CC   
170836  695 2021-09-27 10:00:44.000 1.00    1X6X12  11.62   11.62   1.10    12.72   16749   COUNTER SALE    CS   
170836  695 2021-09-27 10:00:44.000 1.00    1X6X12  11.62   11.62   1.10    12.72   16749   COUNTER SALE    CC

I want to just pull the rows where the method of payment "MOP" is different. I'm using the data to run a report and need it just with distinct or unique MOP's.

Thank You

CodePudding user response：

You could use ROW_NUMBER here an arbitrarily take the "first" record from each MOP group, according to some order:

WITH cte AS (
    SELECT s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice, 
           S.salestax, S.linetotal, S.salenbr, C.company, P.MOP,
           ROW_NUMBER() OVER (PARTITION BY P.MOP ORDER BY S.date) rn
    FROM sales S
    INNER JOIN cust C ON S.custid = C.custid
    INNER JOIN pmts P ON S.salenbr = P.salenbr
    WHERE S.salenbr = 16749
)

SELECT indx, custid, date, qty, item, price, exitprice,
       salestax, linetotal, salenbr, company, MOP
FROM cte
WHERE rn = 1;

CodePudding user response：

So, what you want to do will probably work better using a Common Table Expression or CTE. Something like this:

WITH CTE_Sales AS
(
    SELECT
        s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice, 
        S.salestax, S.linetotal, S.salenbr, C.company, P.MOP, 
        COUNT(1) AS salesCount
    FROM
        sales S
    JOIN
        cust C ON S.custid = C.custid
    JOIN
        pmts P ON S.salenbr = p.salenbr
    GROUP BY
        s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice,
        S.salestax, S.linetotal, S.salenbr, C.company, P.MOP
)
SELECT     
    indx, custid, date, qty, item, price, extprice, 
    salestax, linetotal, salenbr, company 
FROM
    CTE_Sales
GROUP BY 
    indx, custid, date, qty, item, price, extprice, 
    salestax, linetotal, salenbr, company
HAVING 
    salesCount > 1

What this does is that the CTE contains all your data, which makes it easier to not deal with joins every time. You've also done a group by so you know how many records you have for the same sale.

Then, when you pull the data, you're grouping the records without the MOP. Since the first record is grouped with MOP and the second is grouped without, you know that the MOPs are different.