Results 1 to 9 of 9

Thread: Distinct Rows

  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Distinct Rows

    Hi all (PAT)
    I have another sticking point.........

    Is it possible to retrieve Distinct rows from this Select Statement?
    I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.

    Thank you in advance
    Andy

    Code:
    SELECT     dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated, 
                          CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
    FROM         dbo.Customers INNER JOIN
                          dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
                          dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
    WHERE     (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    You can use the aggregation function Min or Max with Group By to get just the Id one of the orders:
    SQL GROUP BY Statement

    Hope this helps.

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    I'm not sure how i would write that imex
    Could you offer a sample please?

    Many thanks
    Andy

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If all you need is unique rows (this type of request almost always starts that way then slides into oblivion of additional requirements), I'd cheat and use
    Code:
    SELECT DISTINCT
       Customers.CustomerID, Customers.Title, Customers.FirstName
    ,  Customers.LastName, Customers.CustomerEmail, Customers.DateCreated, 
    ,  CONVERT(char, Customers.DateCreated, 103) AS [DD/MM/YYYY]
    ,  loyalty_points.LPoints, Orders.OrderID
       FROM dbo.Customers AS Customers
       INNER JOIN dbo.loyalty_points AS Loyalty_Points
          ON Customers.CustomerID = loyalty_points.CustomerID
       INNER JOIN dbo.Orders AS Orders
          ON Customers.CustomerID = Orders.CustomerID
       WHERE 6 = DateDiff(day, Customers.DateCreated, GETDATE())
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    Thanks again PAT for taking time to help me out (it's very much appreciated).

    I tried your suggestion and got the same results.
    I get four rows, but three of them are from the same CustomerID.
    I'm guessing this is because, this particular customer placed three orders that day.
    My aim to is find new 6 day old customers and email them once, hence the need for the email address. If i use this code, the same customer will receive the same email three times.

    Thank You
    Andy

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    One question ... you need this query to return the OrderID? This column will be used?

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    Hi imex

    You're quite right!
    I don't need the orderid returning, but do need to know that there is an order placed by the new customer (which is now does if i remove the orderid from the select statement).
    Customers can register without placing an order, but with the code below, everything seems to be correct now :-)

    Thanks again to yourself and Pat

    Andy

    Code:
    SELECT DISTINCT 
                          Customers.CustomerID, Customers.Title, Customers.FirstName, Customers.LastName, Customers.CustomerEmail, Customers.DateCreated, CONVERT(char, 
                          Customers.DateCreated, 103) AS [DD/MM/YYYY], Loyalty_Points.LPoints
    FROM         dbo.Customers AS Customers INNER JOIN
                          dbo.loyalty_points AS Loyalty_Points ON Customers.CustomerID = Loyalty_Points.CustomerID INNER JOIN
                          dbo.Orders AS Orders ON Customers.CustomerID = Orders.CustomerID
    WHERE     (6 = DATEDIFF(day, Customers.DateCreated, GETDATE()))

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have a working solution so consider this to be a "fine point", not a reason for you to change anything.

    I prefer the simplest possible SQL statement that will do the job at hand. Sometimes that requires me to redefine the job a bit (narrowing or broadening the scope).

    In this case, I would prefer using:
    Code:
    SELECT
       Customers.CustomerID, Customers.Title, Customers.FirstName
    ,  Customers.LastName, Customers.CustomerEmail, Customers.DateCreated, 
    ,  CONVERT(char, Customers.DateCreated, 103) AS [DD/MM/YYYY]
       FROM dbo.Customers AS Customers
       WHERE 6 = DateDiff(day, Customers.DateCreated, GETDATE())
          AND EXISTS (SELECT *
             FROM dbo.Orders AS Orders
             WHERE  Customers.CustomerID = Orders.CustomerID)
    Note also that if larger numbers of rows were being used, I would encourage you to use a more complex SQL syntax because it could use an index on the Customer.DateCreated column to drastically improve performance for large record sets. That would be something like:
    Code:
    SELECT
       Customers.CustomerID, Customers.Title, Customers.FirstName
    ,  Customers.LastName, Customers.CustomerEmail, Customers.DateCreated, 
    ,  CONVERT(char, Customers.DateCreated, 103) AS [DD/MM/YYYY]
       FROM dbo.Customers AS Customers
       WHERE Customers.DateCreated BETWEEN
          DateAdd(day, -6, GETDATE()) AND DateAdd(day, -5, GETDATE())
          AND EXISTS (SELECT *
             FROM dbo.Orders AS Orders
             WHERE  Customers.CustomerID = Orders.CustomerID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2008
    Posts
    120
    Thanks again guys
    Very helpful as usual and very much appreciated

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •