Page 1 of 1
Determine highest public poll id mixed with private polls?
Posted: Wed Dec 24, 2008 4:59 pm
by JAB Creations
My current DB/table setup is as described in the
concept thread.
Is there a single query method that I can choose the last/highest ID determined in the table?
For example I'd user
WHERE public='1' though I'm not sure how to return only the last public poll. For example lets say there are 27 polls though the last/highest poll number is 12.
Private polls have a
public row value of '0'.
Re: Determine highest public poll id mixed with private polls?
Posted: Wed Dec 24, 2008 5:17 pm
by JAB Creations
I enjoy how for the most part MySQL has been easy to learn.
Code: Select all
SELECT MAX(id) FROM poll WHERE public='1'
Re: Determine highest public poll id mixed with private polls?
Posted: Wed Dec 24, 2008 6:44 pm
by JAB Creations
New issue...
For testing purposes the following poll ID's have public set as so...
1:1
2:0
3:1
4:0
1 is public, 0 is private, this way the only desirable poll id is 3...and I have a newer private poll as well as earlier public polls; in essence a good test case.
This doesn't work though if it did then I'd only need a single query to achieve what I'm trying to do...
Code: Select all
SELECT MAX(po.id), po.question, pc.choice FROM poll AS po LEFT JOIN poll_choices AS pc ON (po.id = pc.id_poll) WHERE public='1'
Then phpMyAdmin suggested I use
GROUP BY...
Code: Select all
SELECT MAX( po.id ) , po.question, pc.choiceFROM poll AS poLEFT JOIN poll_choices AS pc ON ( po.id = pc.id_poll )WHERE public = '1'GROUP BY pc.id
However this returns the following...
+--------------+-------------------------------------------+--------------------
-----------------+
| MAX( po.id ) | question | choice
|
+--------------+-------------------------------------------+--------------------
-----------------+
| 1 | What do you think of Version 2.9 Alpha 2? | NULL
|
| 3 | What do you think of Version 2.9 Alpha 4? | I love the new feat
ures! |
| 3 | What do you think of Version 2.9 Alpha 4? | Looks good for an a
lpha. |
| 3 | What do you think of Version 2.9 Alpha 4? | When does public te
sting begin? |
| 3 | What do you think of Version 2.9 Alpha 4? | I'm not sure what I
'm looking at?
|
| 3 | What do you think of Version 2.9 Alpha 4? | Not sure, just want
ed to vote! |
+--------------+-------------------------------------------+--------------------
-----------------+
6 rows in set (0.00 sec)
I'm also questioning the repetitive return of the poll question in regards to my table structure...but that's secondary.
Thoughts please?