Mailing List Archive


Back to the month index Back to the list index
Andy Hughes (PLZAWH@pln1.life.nottingham.ac.uk)
Fri, 11 Apr 1997 17:00:24 GMT0BST 

From: "Andy Hughes" <PLZAWH@pln1.life.nottingham.ac.uk>
Date: Fri, 11 Apr 1997 17:00:24 GMT0BST
Subject: [mSQL] Primary keys (probably a stupid question)
Message-Id: <69179AE6C85@pln1.life.nottingham.ac.uk>

Hi all,
sorry to post this - I'm sure I am doing
something stupid but I'm running out of
ideas!

I'm using 2.0 beta 4 on RedHat Linux 4.1

I have a table as shown below:

# Table structure for table 'Proj_Adm'
#
CREATE TABLE Proj_Adm (
  Username CHAR(10) NOT NULL,
  Password CHAR(10) NOT NULL,
  Longname CHAR(30) NOT NULL
) \g

CREATE UNIQUE INDEX Administrator ON Proj_Adm (
        Username
) \g

#
# Dumping data for table 'Proj_Adm'
#

INSERT INTO Proj_Adm VALUES ('Test2','Test2','Test2')\g
INSERT INTO Proj_Adm VALUES ('Test1','Test1','Test1')\g
INSERT INTO Proj_Adm VALUES ('test','teste','test')\g
INSERT INTO Proj_Adm VALUES ('Test5','Test5','Test5')\g
INSERT INTO Proj_Adm VALUES ('Test3','Test3','Test3')\g
INSERT INTO Proj_Adm VALUES ('Test7','Test7','Test7')\g
INSERT INTO Proj_Adm VALUES ('Test8','Test8','Test8')\g
INSERT INTO Proj_Adm VALUES ('Test4','Test4','Test4')\g
INSERT INTO Proj_Adm VALUES ('Testa','Testa','Testa')\g
INSERT INTO Proj_Adm VALUES ('Testb','Testb','Testb')\g
INSERT INTO Proj_Adm VALUES ('Testc','Testc','Testc')\g
INSERT INTO Proj_Adm VALUES ('Test6','Test6','Test6')\g
INSERT INTO Proj_Adm VALUES ('Testd','Testd','Testd')\g

Now, all I want to do is get all the records
in primary key order.

When I used ORDER BY Username it worked but
didn't return the Username values.

I then tried

ORDER BY Administrator,
WHERE Administrator like '%',
WHERE Administrator like Username

plus variants, with and without brackets and
I always get a message back saying

unknown field Administrator

I guess I have to somehow tell the system
that Administrator is a primary key field
and not a regular data field. I've gone
through the FAQ, all I found was:

How does SELECT return rows?

On Tue, 4 Jul 1995, Karri-Pekka Laakso wrote:

    Does SELECT return the rows always in order 'first inserted first',
    if there is no ORDER statement given, and the rows are selected
    from one table only, and there has been no DELETEs on that table?
    It seems be so, but is it guaranteed?

David Hughes replied:

    This is guaranteed. The only time the rows will be returned in
    another order is if you have deleted a row and it's then filled by
    a later insert.

which confirms I do need to somehow order
the row retrieval.

If anyone could save my sanity by explaining
what I am doing wrong, I'd be very grateful!

Thanks in advance,
   cheers,
      Andy.
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!