SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to pull MAX(id) that is NOT NULL

    So this is focused on pulling the "pdf_url" value from the presentation table. I want to pull the newest "pdf_url" for each company, so I am using the MAX() function.

    I am using Soda companies as an example. Pepsi, has three urls and I want to pull the newest pdf_url that is not NULL. Coke has one row, but is missing a pdf_url, but my SQL statement is pulling that row as empty. RC Cola, is working fine.

    Here is my SQL:
    Code SQL:
    SELECT pdf_url FROM presentation WHERE id = (SELECT MAX(id) FROM presentation WHERE exec_url IS NOT NULL AND id=$value)

    Here is my table structure:
    Code:
    company table
    =============================
    company_id |	name	
    =============================
    1	   |	Pepsi			
    2	   |	Coke
    3	   |	RC Cola				
    			
    
    presentation table
    ====================================================
    id  |  company_id |   pdf_url 	|  presentation_url
    ====================================================
    1  |	1	  |  file1.pdf  |  pres1.html
    2  |	2	  |  NULL       |  pres2.html	
    3  |	1	  |  file2.pdf  |  pres3.html	
    4  |	3	  |  file3.pdf  |  pres4.html	
    5  |	1	  |  NULL	|  pres5.html

    With the above SQL I am incorrectly getting these row id's from the presentation table:
    2, 4, 5

    I want to get the rows from the presentation table:
    3, 4

    In my SQL it seems like I am not properly filtering out the NULL values, which is causing the issues. Its like I want to "Give me all the rows from the presentation table where pdf_url is not null, then with those results, give me the MAX(id) for each company_id".

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your query uses some column "exec_url" which isn't in your tables?

    Does this work?

    Code:
    SELECT
      pdf_url
    FROM
      presentation
    WHERE
      id = (SELECT MAX(id) FROM presentation WHERE pdf_url IS NOT NULL GROUP BY company_id)

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh, yea sorry, exec_url is actually pdf_url, I changed it in my examples to be easier to understand...let me try your statement..

  4. #4
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I'm pretty sure I got it! I needed to put IS NOT NULL in both SQL statements like this.

    Code SQL:
    SELECT pdf_url FROM presentation WHERE pdf_url IS NOT NULL AND id = (SELECT MAX(id) FROM presentation WHERE pdf_url IS NOT NULL AND company_id=$value)


Bookmarks

Posting Permissions

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