vb.org Archive - MyChenQL, part 7

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - MyChenQL, part 7 (https://vborg.vbsupport.ru/showthread.php?t=47335)

filburt1

01-03-2003 06:27 PM

MyChenQL, part 7

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

01-03-2003 06:37 PM

just a thought..

dont know if its possible...

Somehow you could use the array_unshift function?

- miSt

filburt1

01-03-2003 06:38 PM

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

Dean C

01-03-2003 06:45 PM

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

01-03-2003 08:20 PM

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:

Code:

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

01-03-2003 10:51 PM

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

something like that?

filburt1

01-03-2003 10:54 PM

Don't think it's working:

Code:

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

01-03-2003 11:01 PM

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

filburt1

01-03-2003 11:02 PM

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

Scott MacVicar

01-03-2003 11:04 PM

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

01-05-2003 12:46 AM

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

01-05-2003 12:49 AM

Idea didn't work:

Code:

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

01-05-2003 01:29 AM

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

Xenon

01-05-2003 10:04 AM

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

filburt1

01-05-2003 02:11 PM

Nope.

All times are GMT. The time now is 02:01 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01197 seconds Memory Usage 1,740KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (15)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: