sql select order

[Shawn Anderson]

is there a way to select data from a table in the order that it was inserted?

something like:

select firstname,lastname from customerList order by insertOrder

I'm doing something like this now:

select  * from motility where subID="123" and sampleID="abc"

I'm not really sure how the select is sorting the results, but it isn't doing it
in the natural format.

If there isn't a specific way, I'll need to add another column with an autonumber or something so I can pull in that order later.

[Joseph Cote]

As far as mainframe DB2 goes, which is what I use and am familiar with, there is an explicit statement to the effect of: Unless you use "order by" there is absolutely no guarantee of the order the result set is in.

If you need a certain order guaranteed, best bet is to have a key it can sort on.

[Edwin Knoppert]

Usually there is a key (long integer/autonumber).
But there are also key's you barely can't sort like guid's

Depending on the database you may be lucky.
For some DB's you can not reliably obtain records in the original order.

[Chris Holbrook]

It may be worthwhile to look at your database's documentation for ROWID, which may be, or have a component which is, incremented for each row added.

[Shawn Anderson]

thanks everybody.
I should mention I'm using MySql 4.1.21.
They don't have a RowID:

http://forums.mysql.com/read.php?61,25654,27273#msg-27273

I can implement an auto_increment field, just wondering if there was an existing way.

[Mike Doty]

SQLite

Would have to hear from one of the gurus if this always holds true.
Below I've created a table 4 ways and the records come back
in the order entered unless PRIMARY KEY is used.

#INCLUDE "..\Inc\SQLitening.Inc"
FUNCTION PBMAIN () AS LONG
  LOCAL sIP$,rsModChars$,PortNumber&, sSql$, s$
  sIp           = "LocalHost"
  'slConnect(sIP,PortNumber,rsModChars) 'not required if LocalHost
  rsModChars = "C"
  slOpen ("table1",rsModChars)
  slExe "Drop Table If Exists table1"
  'slExe "Create Table If Not Exists table1(f1 integer primary key,f2,f3)" 'records come back 1,2,3,4
  slExe "Create Table If Not Exists table1(f1 integer key,f2,f3)"          'records come back 2,1,4,3
  'slExe "Create Table If Not Exists table1(f1,f2,f3)"                     'records come back 2,1,4,3
  'slExe "Create Table If Not Exists table1(f1 integer,f2,f3)"             'records come back 2,1,4,3
 
  s = "2" + $NUL + "First insert"    + $NUL + "TWO"
  slExe(slBuildInsertOrUpdate("table1",s))
 
  s = "1" + $NUL + "Second insert"   + $NUL + "ONE"
  slExe(slBuildInsertOrUpdate("table1",s))
 
  s = "4" + $NUL + "Third insert"    + $NUL + "FOUR"
  slExe(slBuildInsertOrUpdate("table1",s))
 
  s = "3" + $NUL + "Fourth insert"   + $NUL + "THREE"
  slExe(slBuildInsertOrUpdate("table1",s))
 
  'slSel "Select * FROM table1"
  slSel "Select * FROM table1 order by f1"
  'slF, slFN, slFX, slFNX
  DO WHILE slGetRow
    ? slF(1)+ " " + slF(2)+ " " + slF(3)
  LOOP
  slDisconnect
  SLEEP 3000
END FUNCTION

[Michael Mattias]

> there a way to select data from a table in the order that it was inserted?

Not unless the 'insert order' is a column. ROWID may or may not be such a column depending on DBMS. (eg, when a ROWID is deleted, it may get re-used by a row inserted later).

ROWID is NOT an 'SQL standard' data field.

Just add a timestamp column to your data row and include that when inserting. You'll probably need that for some other new purpose next month anyway.

MCM

[Wayne Suite]

Sql Sort

Why could'nt you add ORDER by Date since most likely every customer order would have a date in the database?

[Michael Mattias]

Why could'nt you add ORDER by Date since most likely every customer order would have a date in the database?

Orders, yes, but example was

>select firstname,lastname from customerList order by insertOrder

While many systems do have a "date added" or "open date" in their customer master file, not all of 'em do.

If the order in which added is important, it's important enough that some kind of 'sequence' or 'timestamp' explicitly be part of the data.

Strange how that works out.... you just can't seem to get the output you want unless the data you need are available as an input.

I think this night be related to that "think before you code" thing......

[Shawn Anderson]

Yes there is a date stamp but that's not helpful here because there could be many entries for a given day.

I think this night be related to that "think before you code" thing......

I'd love to.
If I could only pry out of my customers what they wanted BEFORE I did any coding, it would certainly be a push in the right direction.

This particular project has the worst case of "scope creep" that I've ever experienced. They do pay by the hour however, so it's not all bad....

[Michael Mattias]

If I could only pry out of my customers what they wanted BEFORE I did any coding, it would certainly be a push in the right direction.

Mine do. I make them sign off on specs.

This particular project has the worst case of "scope creep" that I've ever experienced.
They do pay by the hour however, so it's not all bad....

About 75% of my work I quote fixed price for fixed product... the specs they signed off on. If they want a change after they sign off, I either have them sign off on that, which authorizes extra charges, OR ... I tell them I will be happy to add that as a chargeable item - AFTER I deliver the version they already signed off on.

In general I won't accept a 'change' if I'm already past the point where I would have had to allow for it. If it doesn't cause me to have to go back and retest everything I will agree to add the feature before initial delivery.

Usually what I do is make the first "deliverable" the detail specs... with a portion of the total fee due on delivery of those specs.

This not only eliminates any confusion about "what the software will do,", it "vests" the client in the project, so when questions do come up (and they always do), I get answers or decisions very quickly.

Of course, the key to all this is being able to create those specifications. Usually I use what will eventually will be split into two piece of documentation... the "user" documentation and the 'technical' documentation. Put those together they make nice specs.


MCM

[Shawn Anderson]

It's really not that bad, just this one customer.

Originally, they came to me with specific details of what they wanted, which I did. Once they saw it working, their minds started working and they thought, "If we can do this, why not do THIS too?". My answer is always the same: we can do whatever you want, it just costs in time (which equates to money). A project that I quoted 30 hours on will end up closer to 300.

In general I won't accept a 'change' if I'm already past the point where I would have had to allow for it.

Wow, you're tough! Why not just do it and charge them for it? It all pays the same, right? Gosh, reading that sentence just now sounds greedy of me, but I just look at it at giving them what they want, unless of course they want to do something that you consider bad practice.

By now, this particular customer understands that we are way off of the original concept. I've resorted to breaking it down to VERY small steps. I do an entry screen and let them OK it before I make any of the buttons work, etc. (this is a web application project btw).

[Cliff Nichols]

Must be nice to work for yourself rather than being stuck with someone above you pass the work to you.

If I could only pry out of my customers what they wanted BEFORE I did any coding, it would certainly be a push in the right direction.

By the time I see anything to do with me, it becomes my problem, and the customer thinks the "Concept is proprietary"????? when in my case, to me its more like "Slide moves out, Slide moves in, Rotary Table go round and round" It all depends on what sequence you need it to do it in??? so what is so secret???

Mine do. I make them sign off on specs.

Must be nice...most the time by the time I see it, they not only don't really know what they want, but think they want something else, and are wrong

This particular project has the worst case of "scope creep" that I've ever experienced.

That bad eh??? (I am sure you have seen doozies worse, but just since you are focused on the one problem then this one is worse???? or god forbid it TRUELY is the worst yet

In general I won't accept a 'change' if I'm already past the point where I would have had to allow for it. If it doesn't cause me to have to go back and retest everything I will agree to add the feature before initial delivery.

Thats nice to know...at least flexible to a point, and I agree with you... if the "Scope Creep" is minor, then whats the harm in making the customer happy?

Once they saw it working, their minds started working and they thought, "If we can do this, why not do THIS too?"

Ummm maybeeeee...depending on the amount of creep it causes and what you can afford

and whats worse is...."Well you promised this" and program meets needs...."But how come you didn't do this??" and answer is...."You did not ask for that, you did not even HINT at that.....nor did the idea for it even hit you till I delivered what you spec'ed"

Sorry but sore subject for me today that for the 5th time something returned because "Does not meet specs", and highers up put it on me to prove or disprove, and the customer is insistent we pay for tests they did 3rd party to prove us wrong (or even more to the point (and YES I am taking this personally me wrong) and after testing all pieces possible (not including how they tested, cause they won't tell me), Nothing is wrong...it works as advertised, and even well above what was originally spec'd ..... so show me how I am wrong, or pay me to fix what you THINK is wrong)


Sorry about that....like I said "Sore Subject" but like "Red Green" says on PBS "We are all in this together"

[Michael Mattias]

I meant, I won't accept the change except later, after I deliver.

First of all, it does not pay the same. When you quote fixed price for stuff, any deviation is a potential argument.

Second, I've usually guaranteed a delivery date... which won't be met because of additional work.

I do work for some nine-figure companies, which means it's not just John and Mary who look at my ability to deliver on-time and in-budget. Hard to believe, but John and Mary don't always tell Mr. Larger Office that they've changed specs in mid-stream (because now they make themselves look, er, 'less than optimal')

Third, I've usually guaranteed a delivery date, at which delivery time I have promised someone else I would work on their project to meet their promised delivery date, which now won't be met.

Must be nice to work for yourself rather than being stuck with someone above you pass the work to you.

Yes.

[Shawn Anderson]

I'll accept whatever change they want to do, but always bill for it unless of course it was obviously my mistake. My biggest complaint is when projects go on and on, with no end. They start to encroach on other scheduled projects.

It wasn't hyperbole. This IS the worst case of creep I've ever encountered.

[Michael Mattias]

BTW... yes, this situation (scope creep) is covered in my standard software development contract, to which the client has agreed before work started.

If anyone is interested, I'd be happy to supply my boilerplate agreement (a "fill in the blanks" thing) in MS-Word format. Just drop me an email and I'll attach to a 'reply' . Feel free to suck out any paragraphs you like.

Fair Advance Warning: I do not retype email addresses, it has to be 'reply' or a 'clickable link' in the text and the file goes as an "attachment. " YOU are responsible for 'whitelisting" or whatever you have to do to receive it.

[Michael Mattias]

Would have to hear from one of the gurus if this always holds true.
Below I've created a table 4 ways and the records come back
in the order entered unless PRIMARY KEY is used.

No, it does not always hold true.

If you really want to know in what order the DMBS is going to return the data you'd have to check the "plan."

Plan details are available via an SQL "EXPLAIN" command, if your DBMS and the interface you are using support that.

But bottom line if you want result sets ordered, you have to use "order by" in your SQL statement and if you want to use "order by" you need to have columns on which to order.


MCM