SQL Server return distinct rows?

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

2 answers

  • answered 2021-09-27 15:57 Tim Biegeleisen

    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;
    

  • answered 2021-09-27 16:06 yoelbenyossef

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum