Professional Web Applications Themes

why I couldn't get the same order of retrieved records as inserting them? - Microsoft SQL / MS SQL Server

Hi, I have a table with 2 fields (firstname lastname and companyname), but no key. I am using SQL Server 7. If I insert 2 records one by one from an ASP script. I inserted "John", "Mortem" and "AJ company" firstly and then "Lisa", "Wang" and "AJ company". And then I try to retrieve the two records by "SELECT * FROM tableNames WHERE companyname='AJ company'". I think the result should be (because I save "John Mortem" firstly) : John Mortem AJ company Lisa Wang AJ company But in fact, the result may be: Lisa Wang AJ company John Mortem AJ ...

  1. #1

    Default why I couldn't get the same order of retrieved records as inserting them?

    Hi,

    I have a table with 2 fields (firstname lastname and companyname), but no
    key. I am using SQL Server 7.

    If I insert 2 records one by one from an ASP script. I inserted "John",
    "Mortem" and "AJ company" firstly and then "Lisa", "Wang" and "AJ company".

    And then I try to retrieve the two records by "SELECT * FROM tableNames
    WHERE companyname='AJ company'".

    I think the result should be (because I save "John Mortem" firstly) :
    John Mortem AJ company
    Lisa Wang AJ company

    But in fact, the result may be:
    Lisa Wang AJ company
    John Mortem AJ company

    The order is different. How can I get the correct order in the sql result:
    saved earlier, retrieved earlier?


    Thanks


    Qing





    Rabbit63 Guest

  2. #2

    Default Re: why I couldn't get the same order of retrieved records as inserting them?

    A table is defined as an "unordered set of rows". The ONLY way to guarantee
    a sort order is to include an ORDER BY clause in your query.

    Bob Barrows

    Rabbit63 wrote: 


    Bob Guest

  3. #3

    Default Re: why I couldn't get the same order of retrieved records as inserting them?

    In addition to what Bob has said, *every* table should have a primary key.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default why I couldn't get the same order of retrieved records as inserting them?


    Hi,

    Alter the table by adding an identiy col and you can
    retrieve the results sorted by identity col.

    Kn 
    companyname), but no 
    inserted "John", 
    and "AJ company". 
    FROM tableNames 
    Mortem" firstly) : 
    in the sql result: 
    kannan Guest

  5. #5

    Default why I couldn't get the same order of retrieved records as inserting them?

    create another column in your table that contains a
    datetime variable. Have that new datetime column default
    to the current time. This will allow you to do a select
    using the new column to order your date by using this new
    datetime column in your order_by clause. 
    companyname), but no 
    inserted "John", 
    and "AJ company". 
    FROM tableNames 
    Mortem" firstly) : 
    in the sql result: 
    Greg Guest

Similar Threads

  1. inserting records
    By tapping in forum Coldfusion Database Access
    Replies: 5
    Last Post: September 29th, 02:19 PM
  2. Web Service CFC: Inserting multiple records
    By GiantGinkgo in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: July 18th, 01:57 PM
  3. Error inserting records
    By Vincent in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 1st, 01:13 AM
  4. serialization errors when inserting new records
    By Ralph van Etten in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: January 24th, 10:28 AM
  5. Inserting blank records
    By david preswick in forum FileMaker
    Replies: 1
    Last Post: August 31st, 02:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139