Hi

I have 2 tables Dealsbasket and Product

Deals Basket :

DealNumber
DealProduct


Product :

product code
product description

When i display records from deal basket for a specific deal number it
displays the record fine. When i create a view and link the 2 tables
by product code so i can display description the order of records
change????

How can i display the records in the dealsbasket in the order they
were imported into the table and then display prod description as well
with out that order changing.

Im using sql server 2000

thanks

shawrie <tourerukcom (AT) googlemail (DOT) com> writes:

One possibility is that the problem is caused by too many question
marks :-)

Another is that you've forgotten that SQL result sets are allowed to
come back in *any* order, and can change from one moment to the next,
unless you use an ‘ORDER BY’ clause.

The RDBMS has no obligation to even remember what order they were
inserted into the table. Most will not, instead optimising storage
internally for speed purposes.

If you want the records to have a particular sequence, then you need
them to have a field which can be the subject of an ‘ORDER BY’ clause.
Likely candidates are the real-world identifier for the product or deal.

--
\ “Following fashion and the status quo is easy. Thinking about |
`\ your users' lives and creating something practical is much |
_o__) harder.” —Ryan Singer, 2008-07-09 |
Ben Finney

shawrie wrote:

In the hope that this was a homework question, I'll just point out that
SQL databases don't have "records" they have rows, and rows are just
statements of fact. Statements of fact have no associated order; there
is no concept of first fact or last fact or nth fact. They are just
(presumably) true statements. The system is allowed to do anything it
likes in the interests of speed, as long as it always gives you all the
facts you asked for.

SQL databases follow the Information Principle, which requires that all
the infromation content of the database is represented explicitly as
values of attributes of rows *and in NO OTHER WAY*. If one fact must
be distinguised from another in terms of the time (or sequence) at
which it became known, then the time (or sequence) must be part of the
fact. Once you have the sequence information you can use it to order
the facts when you finally report them.

Again in the hope this is a homework question, ask yourself, why was the
Information Principle invented? Why is it a good thing, and not the
pain the backside it might seem at first?

HTH.

Art

[This followup was posted to comp.databases and a copy was sent to the
cited author.]

In article <ituuo7$7cd$1 (AT) speranza (DOT) aioe.org>, art.ward (AT) noreply (DOT) xx says...

....

True, but rather picky. Most working database people use the terms
"rows" and "records" relatively interchangeably. Everybody understands
what you mean either way. The rest of your (snipped) post is qute a
good explanation, though rather abstract in its wording.

For the OP: if you want your records back in a specific order, you MUST
tell it what that order is. If you want it in the order they were
inserted, then there has to be a field that helps the db engine figure
out what that order was. Typical ways of achieving this are timestamps
and automatically incrementing integer keys.

David Kerber wrote:

True, and as long as they know the difference they can get along fine.
But rows aren't records; think of column stores for example.

Most people who already know the difference get away with it either way.

Abstraction is good. Programmmers pursue it relentlessly, for good
reason.

Art

[This followup was posted to comp.databases and a copy was sent to the
cited author.]

In article <itvepc$f6h$1 (AT) speranza (DOT) aioe.org>, art.ward (AT) noreply (DOT) xx says...

....

True, and that's why I said "relatively" interchangeably; you can't
ALWAYS use them interchangeably.


Yep

It's often good for programming though sometimes it can be taken too
far, but not so much for person-to-person communication in many cases.

On Jun 25, 5:33*am, Arthur Ward <art.w... (AT) noreply (DOT) xx> wrote:
From what I've seen of them, SQL db's are rather comparable in what
they are able to record. It is amazing to me that some/most/perhaps
all SQL dbms'es can't make all the inferences the relational algebra
requires. I would have thought that'd be step one in every
implementation, at least where some effort was made to decide just
what are the relational requirements. The bizarre thing is how the
SQL db's and dbms'es all seem to invent a requirement that is nowhere
to be found in the algebra or calculus, aka nulls.

On Jun 25, 2:53*pm, paul c <toledobythe... (AT) yahoo (DOT) ca> wrote:
Actually, nulls are part of the relational model. Codd added nulls to
RM in 1979 along with 3VL and extensions to relational algebra and
calculus to accommodate the new features (google "Extending the
database relational model to capture more meaning".) He later added
two types of nulls.

No doubt SQL implementations have many quirks and limitations. Even
Standard SQL has its weaknesses, e. g., the EXISTS bug.

Even though Codd himself extended the Relational Model in 1979,
opposition to nulls began to form in the 90's; those opposed include
Date, Darwen, Pascal. 3VL was the original sticking point.

--
Lee Fesperman, FirstSQL Software (http://www.firstsql.com)
================================================== ===========
* Pure Java implementation, runs on cellphones to mainframes
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)