SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Dec 4, 2009, 15:26 #1
- 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".
-
Dec 4, 2009, 17:15 #2
- 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)
Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Dec 4, 2009, 17:22 #3
- 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..
-
Dec 4, 2009, 19:04 #4
- 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