This discussion is archived
5 Replies Latest reply: Feb 8, 2013 4:41 PM by ti3r RSS

Need to Select rows with null values in one column  first

ti3r Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Forgot to mark that the question was answered.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points