Can I somehow ignore the post with the earliest date? With SQL or PHP?
SELECT subject, date
FROM posts
WHERE id = $id
ORDER BY date DESC
while ($row = mysql_fetch_array($query)) {
Cheers
-
Could you describe the sample data & expected output?shahkalpesh– shahkalpesh2009-06-26 18:50:41 +00:00Commented Jun 26, 2009 at 18:50
-
You can write a sub-query that fetches the earliest date, and include it in the where clause. I wouldn't have the foggiest idea how to code it.NickSentowski– NickSentowski2009-06-26 18:53:30 +00:00Commented Jun 26, 2009 at 18:53
-
Hopefully, you don't have multiple rows sharing the same id.racerror– racerror2009-06-26 18:57:44 +00:00Commented Jun 26, 2009 at 18:57
Add a comment
|
4 Answers
You could probably write some convoluted sql to do it, or you could just do it in your php:
$first = true;
while ($row = mysql_fetch_array($query)) {
if ( $first ) {
$first = false;
continue;
}
// Process rows here
}
4 Comments
Alex Mcp
HA! I don't program much, and I think that's quite clever. Upvote for you.
Eric Goodwin
If the requirements are as simple as just starting from the second row of the result, then I vote for an in-code solution as well.
GiladG
Shouldn't the row processing be as a part of an "else"? Otherwise, line one will be processed just like the rest.
Eric Petroelje
@GiladG - that's why you have the "continue" statement inside the if. It skips the rest of the loop and starts over at the beginning.
I've got nothing to test this with, but would this work?
SELECT subject, date
FROM posts
WHERE id = $id
OFFSET 1
ORDER BY date DESC
Or for MySQL five compatibility as pointed out in the comments
SELECT subject, date
FROM posts
WHERE id = $id
LIMIT 1,18446744073709551615;
ORDER BY date DESC
The large number was copied exactly from the MySQL docs.
2 Comments
Alistair Knock
Again untested here but that looks good to me, offset treats the first row as 0 and so this should start from the 2nd.
jeroen
I don't think you can do that in mysql. From the manual (version 5.0): "For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax". In other words, you can't use offset by itself.
Assuming date uniquely determines a row for a given id,
SELECT subject, date
FROM posts
WHERE id = $id
AND date NOT IN
( SELECT MIN(date)
FROM posts
WHERE id = $id
)
ORDER BY date DESC
1 Comment
Charles Bretana
For a bit mor3e clarity, I would use "Where date <> (Select Min(Date) ..." instead of "Where Date Not In... "
If you're using SQL 2005 or better, you could use the row_number function...
With MyCTE AS (
SELECT subject,
date,
RowNumber = ROW_NUMBER() OVER(ORDER BY date DESC)
FROM posts
WHERE id = $id)
SELECT *
FROM MyCTE
WHERE RowNumber > 1
ORDER BY date DESC
