MyChenQL, part 7

[filburt1]

How can I INSERT into a table a row...but have that row be inserted into the table as the first row, not the last?

I know I can do ORDER BY but that's not a viable solution for the problem.

[Dean C]

just a thought..

dont know if its possible...

Somehow you could use the array_unshift function?

- miSt

[filburt1]

Well for PHP that would be fine but I don't believe there's an applicable MySQL function...

[Dean C]

Could you query the database to output its structure then use variables for the elements of it then use these elements in an array?

- miSt

[filburt1]

I don't see how that would help :confused:

FWIW I'm trying to give ORDER BY precedence over GROUP BY, and more importantly, not have to do it with multiple queries, temporary tables, and all that extra junk.

Plain-English query: select the greatest datetime from the posts table for each forumid.

Posts table schema:

mysql> describe posts;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| subject | varchar(255) | | | | |
| contents | text | | | | |
| threadid | int(11) | | | 0 | |
| forumid | int(11) | | | 0 | |
| datetime | int(11) | | | 0 | |
| posteruserid | int(11) | | | 0 | |
+--------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

[Scott MacVicar]

SELECT forumid, MAX(datetime) AS datetime FROM posts GROUP BY forumid

something like that?

[filburt1]

Don't think it's working:

mysql> select forumid,max(datetime),subject as datetime from posts group by forumid;
+---------+---------------+------------------------------+
| forumid | max(datetime) | datetime |
+---------+---------------+------------------------------+
| 1 | 1041622515 | first thread |
| 2 | 1041623792 | first thread in second forum |
+---------+---------------+------------------------------+
2 rows in set (0.00 sec)

There shouldn't be any subjects :(

[Scott MacVicar]

where did the subjects as datetime appear from, thats not in my query....

[filburt1]

I added it (see the query above). It seems that GROUP BY only works with the first rows it encounters.

[Scott MacVicar]

explain what you want done in proper english, you never mentioned anything about the subject.

Also use
SELECT forumid, MAX(datetime) as datetime, subject FROM posts GROUP BY forumid

[filburt1]

I want information (for now just SELECT *) about the latest datetime for each forumid.

I have a theory though which I'm trying now.

[filburt1]

Idea didn't work:
mysql> select forumid,datetime,10000000000 - datetime as datetimediff,subject from posts group by forumid order by datetimediff;
+---------+------------+--------------+------------------------------+
| forumid | datetime | datetimediff | subject |
+---------+------------+--------------+------------------------------+
| 2 | 1041623792 | 8958376208 | first thread in second forum |
| 1 | 1041622466 | 8958377534 | first thread |
+---------+------------+--------------+------------------------------+
2 rows in set (0.00 sec)

[filburt1]

*sigh* Unforuntately it looks like I'll have to use a cache of some sort :(

[Xenon]

SELECT forumid, MAX(datetime) as datetime, subject FROM posts GROUP BY forumid ORDER BY datetime

[filburt1]

Nope.