Thanks for comments

You say "Even the MySQL documentation tells us that this ordering will not respect changes to the table."

I am aware of the effects of inserts, deletes and even mods to data IIRC.

I tested that with an insert and it indeed broke the order which only requires re-invoking the alter table command

I am only doing a select and am re-invoking the alter table after enetering the select subroutine. It does not help.

So in SQL Sceibble, lets try the alter table order by name and change the SQL statements. That should demonstrate any problem without my trying to extract "20 lines of code" from my program to reproduce the result.

You pointed to the problem earlier in you post in this thread. Using your already existing example should demonstrate the problem.

You state "Also, your SQL seems faulty to me as the "previous record" seems to skip users:"

create table users ( user_id integer not null primary key, name varcha
+r(16) );

insert into users values (1,'Fred');
insert into users values (2,'Barney');
insert into users values (3,'Wilma');
insert into users values (4,'Betty');
insert into users values (5,'Pebbles');


SELECT *
FROM usersI
WHERE user_id < (SELECT MAX(user_id)
                 FROM users WHERE user_id < 4) 
ORDER BY user_id DESC LIMIT 1
[download]

I just SQL scribbled with your scribble and

SELECT * 
FROM users WHERE user_id >= (SELECT MIN(user_id) 
                             FROM users WHERE user_id > 4) 
ORDER BY user_id ASC LIMIT 1
[download]

with 4 Returned "Pebbles" and with "2" returned "Wilma". That statement is working correctly so I am assuming my other statement will also work correctly.

So are we saying that something in my program is de-ordering? I am not executing anything that would do that as far as I know. I will look further.

Where can I get SQL Scribble? When I searched on it I only saw ref to MS-SQL.

Best regards

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.