5 Replies Latest reply: Feb 8, 2013 6:41 PM by ti3r RSS

    Need to Select rows with null values in one column  first

    ti3r
      I have a table for advertising leads where the Last_Mailed_Date is often null, meaning the individual has never been sent advertising. If I query for leads that have never been mailed (is null) or who have not been mailed within 60 days, it always returns the is null rows last, so depending on number I request, I may never get the "is null" records at all. We want to pull the "is null" rows first because they have never received advertising from us before. I can't find any way to change the order of how the records are selected. Bottom line, I want to pull records with the null values first, then if I need more records, pull with the specified date requirements for last mailed.

      select *
      from T3_LEADS
      WHERE
      Last_Mailed_Date IS NULL OR (SYSDATE - Last_Mailed_Date) > 60)
        • 1. Re: Need to Select rows with null values in one column  first
          sb92075
          NULLS FIRST | NULLS LAST Specify whether returned rows containing null values should appear first or last in the ordering sequence.

          NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.



          ORDER BY Last_Mailed_Date NULLS FIRST
          • 2. Re: Need to Select rows with null values in one column  first
            ti3r
            I am not pulling the entire set of records, only 200 that meet the query criteria. When I added the ORDER BY Last_Mailed_Date NULLS FIRST it still did not select the records with null values.
            • 3. Re: Need to Select rows with null values in one column  first
              Frank Kulash
              Hi,
              ti3r wrote:
              I am not pulling the entire set of records, only 200 that meet the query criteria. When I added the ORDER BY Last_Mailed_Date NULLS FIRST it still did not select the records with null values.
              Show your query, including NULLS FIRST.
              What you posted earlier
              select *
              from T3_LEADS
              WHERE
              Last_Mailed_Date IS NULL OR (SYSDATE - Last_Mailed_Date) > 60)
              has nothing that limits the results to 200 rows.
              Neither NULLS FIRST nor its absence causes any rows not to be displayed. If you're cutting off the results after 200 rows, then you have to sort the results (with NULLS FIRST) before checking for the 200 first rows.
              Once again, post your code. Better yet, post a complete test case that shows the problem. Post CREATE TABLE and INSERT statements for a little sample data and the results you wnat to see from that data. So you don't have to post too much, post a problem where you want to show 5 rows, not 200. You'll get a solution that works for 200.
              See the forum FAQ {message:id=9360002}
              • 4. Re: Need to Select rows with null values in one column  first
                ti3r
                Ah, the light came on with your response. This worked for me:

                select *
                from (
                select *
                from T3_LEADS
                WHERE
                (SYSDATE - Last_Mailed_Date) > 60 OR Last_Mailed_Date is null
                ORDER BY Last_Mailed_Date NULLS FIRST
                )
                where MARKET_ID = :P50_Select_Market AND rownum <= 200

                I had my ORDER BY with the wrong select statement - obviously that did not work! Thanks.

                And I will post any new questions in the format that you stated. Really appreciate your help.
                • 5. Re: Need to Select rows with null values in one column  first
                  ti3r
                  Forgot to mark that the question was answered.