Post reply

Removing records from SELECT

  • September 5, 2017 at 9:45 am

    #348891

    Hi everyone

    I have a table in SQL called dbo.Orders

    The table has the following columns:

    Customer,

    OrderId,

    OrderDate,

    Region,

    Department,

    RegistrationDate

    When a new order has been made a new record is entered in the table. I want to bring back all records with a valid most recent OrderDate plus all other records where there has been no orders so far. My problem is that because the table keeps a record of each order when I select from this table I get multiple rows where a customer has ordered many times. I just want the most recent plus the customers who have not ordered anything - there OrderDate field is NULL.

    Any help greatly appreciated.

    BO

  • September 5, 2017 at 9:57 am

    #1958133

    Without Sample data we can't write a query, however, one option is to use a CTE and put row numbering in, and then LEFT JOIN to Row Number 1. An alternative would be to use OUTER APPLY and TOP 1 with an ORDER BY clause.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • September 5, 2017 at 9:59 am

    #1958134

    Thanks so much for replying so speedily.

    That seems to work perfectly!

    Thanks again

    BO

  • September 5, 2017 at 10:00 am

    #1958135

    Looks like this works...
    You may want to include other rows from both tables, but this should give you the gist of how to do it.
    use AdventureWorks2014;
    GO
    SELECT c.CustomerID
        , s.SalesOrderNumber
    FROM Sales.Customer c
    OUTER APPLY (
    SELECT TOP (1) SalesOrderNumber
    FROM Sales.SalesOrderHeader so
    WHERE so.CustomerID = c.CustomerID
    ORDER BY so.OrderDate DESC ) s;

  • September 5, 2017 at 10:42 am

    #1958138

    Thanks for sending the SQL.

    It works brilliantly and I've adapted my script.

    BO

  • September 5, 2017 at 11:25 am

    #1958150

    pietlinden - Tuesday, September 5, 2017 10:00 AM

    Looks like this works...
    You may want to include other rows from both tables, but this should give you the gist of how to do it.
    use AdventureWorks2014;
    GO
    SELECT c.CustomerID
        , s.SalesOrderNumber
    FROM Sales.Customer c
    OUTER APPLY (
    SELECT TOP (1) SalesOrderNumber
    FROM Sales.SalesOrderHeader so
    WHERE so.CustomerID = c.CustomerID
    ORDER BY so.OrderDate DESC ) s;

    In this particular case, I suspect that the CTE/ROW_NUMBER version will perform better, but you should test both versions.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply