By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

Get the First record

P: n/a
Hi all,

Is there a MySQL function to get the first record through a query? I would like to open a page of
client records with the first one shown. The other records can be accessed from a hyperlinked list.

Thanks for any advice,

Rick
Jul 17 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
Rick wrote:
Is there a MySQL function to get the first record through a
query? I would like to open a page of client records with the
first one shown. The other records can be accessed from a
hyperlinked list.


Define "first record" :)

There is no such thing as far as the database is concerned. If you want
to retrieve a single record from the database say so in the query:

select *one* record from the database:
select <field, list> from <tables> where <conditions>
LIMIT 1

select *the first record* from the database
select <field, list> from <tables> where <conditions>
ORDER BY <sort, fields> limit 1
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--
Jul 17 '05 #2

P: n/a
If you only want to return the first row from an unknown number then add
"LIMIT 1 OFFSET 0" to the SELECT statement. Check out the syntax at
http://www.mysql.com/documentation/m...ax.html#SELECT

--
Tony Marston

http://www.tonymarston.net
"Rick" <RB@newsgroup.net> wrote in message
news:11Y0c.97758$Xp.432840@attbi_s54...
Hi all,

Is there a MySQL function to get the first record through a query? I would like to open a page of client records with the first one shown. The other records can be accessed from a hyperlinked list.
Thanks for any advice,

Rick

Jul 17 '05 #3

P: n/a
I noticed that Message-ID: <11Y0c.97758$Xp.432840@attbi_s54> from Rick
contained the following:
Is there a MySQL function to get the first record through a query? I would like to open a page of
client records with the first one shown. The other records can be accessed from a hyperlinked list.


Data is not stored in any particular order in a database. Therefore the
concept of 'first record' is meaningless.

Presumably you mean the oldest record. Either use a field containing a
timestamp or a primary key that auto increments and order the results by
one of those.

For example

$result = mysql_query("SELECT * FROM table ORDER BY id ");
//default is ascending

//to fetch the first record
$myrow = mysql_fetch_array($result)
print $myrow["fieldname1"];

//loop through rest of records
while($myrow = mysql_fetch_array($result))
{
//do stuff with records
}


--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #4

P: n/a
Geoff Berrow wrote:
I noticed that Message-ID: <11Y0c.97758$Xp.432840@attbi_s54> from Rick
contained the following:
Is there a MySQL function to get the first record through a query? I
would like to open a page of client records with the first one
shown. The other records can be accessed from a hyperlinked list.


Data is not stored in any particular order in a database. Therefore
the
concept of 'first record' is meaningless.


Actually, this is not always true... While data may be stored unordered, the
data may also be stored in an ordered manner, usually in the order defined
by an index on the relation.
Jul 17 '05 #5

P: n/a
in which case you still are talking about random records, but indexed by an
indexfield....
First record, still is something undefined, whereas record with lowest
indexnumber might have a "first record" feel to it.

Michel

"Agelmar" <if**********@comcast.net> wrote in message
news:c2*************@ID-30799.news.uni-berlin.de...
Geoff Berrow wrote:
I noticed that Message-ID: <11Y0c.97758$Xp.432840@attbi_s54> from Rick
contained the following:
Is there a MySQL function to get the first record through a query? I
would like to open a page of client records with the first one
shown. The other records can be accessed from a hyperlinked list.
Data is not stored in any particular order in a database. Therefore
the
concept of 'first record' is meaningless.


Actually, this is not always true... While data may be stored unordered,

the data may also be stored in an ordered manner, usually in the order defined
by an index on the relation.

Jul 17 '05 #6

P: n/a
Agelmar wrote:
Actually, this is not always true... While data may be stored unordered, the
data may also be stored in an ordered manner, usually in the order defined
by an index on the relation.


The key word there is *may*.

And the DB server *may* return the records in the order they are stored.
Suppose you have a table with a ID (auto_increment primary key) column.

You have no guarentee that
"select id from table where id between 7890 and 7891"

will return the record with ID 7890 before the other record; the result
*could* very well be
+------+
| ID |
+------+
| 7891 |
| 7890 |
+------+
and *may* even change between calls to the same query!

So, if need the records returned in a specific order, specify that
order in the select command.
<?php
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 0;
$sql = 'select id, name, email from people limit ' . ($page*20) . ', 20';
// ...
?>

does *NOT* guarantee you will not see the same people on page 7 as you
saw on page 6 (or does it?).
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--
Jul 17 '05 #7

P: n/a
Pedro Graca wrote:
Rick wrote:
Is there a MySQL function to get the first record through a
query? I would like to open a page of client records with the
first one shown. The other records can be accessed from a
hyperlinked list.

Define "first record" :)

There is no such thing as far as the database is concerned. If you want
to retrieve a single record from the database say so in the query:

select *one* record from the database:
select <field, list> from <tables> where <conditions>
LIMIT 1

select *the first record* from the database
select <field, list> from <tables> where <conditions>
ORDER BY <sort, fields> limit 1


What I want to do is fill a page that displays record details with some data even if the user did
not select a customer id. I just want to grab one record...either first by id or last name (asc),
but one record only.

The 'Limit' clause as suggested by both Pedro and Tony appears to be what I want.

Thanks,

Rick
Jul 17 '05 #8

P: n/a
I noticed that Message-ID: <JB41c.166553$uV3.715478@attbi_s51> from Rick
contained the following:
What I want to do is fill a page that displays record details with some data even if the user did
not select a customer id. I just want to grab one record...either first by id or last name (asc),
but one record only.
Yes but you also said,

"The other records can be accessed from a hyperlinked list."

I assumed you wanted to show the full details of one record and possibly
a summary of all others such that you could simply click them and get
the details of that record. How are you going to create the list if you
have only accessed one row using LIMIT ?
The 'Limit' clause as suggested by both Pedro and Tony appears to be what I want.


Not if I have understood your scenario correctly.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #9

P: n/a
Pedro Graca wrote:
Agelmar wrote:
Actually, this is not always true... While data may be stored
unordered, the data may also be stored in an ordered manner, usually
in the order defined by an index on the relation.


The key word there is *may*.

<snip>
No arguments. I was just responding to the specific sentence "Data is not
stored in any particular order in a database."
Jul 17 '05 #10

P: n/a
I noticed that Message-ID: <c2*************@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
The key word there is *may*.

<snip>
No arguments. I was just responding to the specific sentence "Data is not
stored in any particular order in a database."


Which is /still/ true.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #11

P: n/a
Geoff Berrow wrote:
I noticed that Message-ID:
<c2*************@ID-30799.news.uni-berlin.de> from Agelmar contained
the following:
The key word there is *may*.

<snip>
No arguments. I was just responding to the specific sentence "Data
is not stored in any particular order in a database."


Which is /still/ true.


Actually, it is true. Data is *always* stored in /some/ order in a database.
On commercial databases, you can specify the order (e.g. you can specify
that tuples should be stored clustered by R.a, and then if you have a B+
index on R.a, you can evaluate range predicates much more quickly, because
you fetch the tuples using sequential I/O rather than random I/O). Even when
the user does not specify a particular storage ordering (or is not allowed
to), the DBMS will itself choose an ordering. Some systems may simply choose
to store tuples in the order in which they are inserted, either compacting
upon deletion or filling-in on updates if the tuples are of fixed size...
but either way, that is a well-defined ordering. Given a transaction log, I
can tell you how the tuples are laid out on disk. (Other systems may store
tuples ordered by the primary key of the relation, etc, but no matter what,
there is *some* well-defined method for determing the order in which tuples
are stored on disk. It might not be an order relating to the time at which
the tuples were inserted, it might not be an order relating to an attribute
of the tuples, but there is an order.)

Granted, if you do not specify an ordering in your query, there is no
guarantee that the tuples will be returned in a specific order, but the
statement was about how tuples were laid out on disk.
Jul 17 '05 #12

P: n/a
I noticed that Message-ID: <c2*************@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
Granted, if you do not specify an ordering in your query, there is no
guarantee that the tuples will be returned in a specific order, but the
statement was about how tuples were laid out on disk.


I'd hardly call ordering records on a disk using a hashing algorithm
'order', but I'm on shaky ground at this level of database theory so
I'll not labour the point.

Suffice to say we both agree that if you want to return the 'first'
record, one has to provide the database with some mechanism for doing
that.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #13

P: n/a
Geoff Berrow wrote:
I noticed that Message-ID:
<c2*************@ID-30799.news.uni-berlin.de> from Agelmar contained
the following:
Granted, if you do not specify an ordering in your query, there is no
guarantee that the tuples will be returned in a specific order, but
the statement was about how tuples were laid out on disk.


I'd hardly call ordering records on a disk using a hashing algorithm
'order', but I'm on shaky ground at this level of database theory so
I'll not labour the point.

Suffice to say we both agree that if you want to return the 'first'
record, one has to provide the database with some mechanism for doing
that.


Definitely... if you want your result to come to you in some particular
fashion, it's up to you to ensure that. As for ordering records on a disk
using a hashing algorithm - I've never seen that before... I don't think you
would ever want to do that, either. Ordering on the attributes that are a
function of the hash should have the same effect, and still provide some
utility for range predicates. The entire purpose of physically storing the
tuples in a sorted order is so that when you retrieve a set of tuples with a
particular (range of) values, you perform sequential I/O, issuing one
request to grab all the blocks, rather than one request per tuple and
thrashing the disk.

P.s. this is my area of research :-) (DBMSs)
Jul 17 '05 #14

P: n/a
I noticed that Message-ID: <c2*************@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
As for ordering records on a disk
using a hashing algorithm - I've never seen that before... I don't think you
would ever want to do that, either. Ordering on the attributes that are a
function of the hash should have the same effect, and still provide some
utility for range predicates. The entire purpose of physically storing the
tuples in a sorted order is so that when you retrieve a set of tuples with a
particular (range of) values, you perform sequential I/O, issuing one
request to grab all the blocks, rather than one request per tuple and
thrashing the disk.

P.s. this is my area of research :-) (DBMSs)


I could tell, the only other person I ever heard call them tuples was my
university lecturer. I'll have to look out my old notes, but the
hashing stuff sticks in my memory. Maybe I misunderstood it. I did
find this on the 'net though:-

File Organization Techniques
Hashing - Each record is placed in the database at a location whose
address is computed as some function (hash function) of that record
(hash field). To store the record, the DBMS computes the hash address
for the new record and instructs the file manager to place the record at
that position

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #15

P: n/a

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:tf********************************@4ax.com...
I noticed that Message-ID: <c2*************@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
As for ordering records on a disk
using a hashing algorithm - I've never seen that before... I don't think youwould ever want to do that, either. Ordering on the attributes that are a
function of the hash should have the same effect, and still provide some
utility for range predicates. The entire purpose of physically storing thetuples in a sorted order is so that when you retrieve a set of tuples with aparticular (range of) values, you perform sequential I/O, issuing one
request to grab all the blocks, rather than one request per tuple and
thrashing the disk.

P.s. this is my area of research :-) (DBMSs)


I could tell, the only other person I ever heard call them tuples was my
university lecturer. I'll have to look out my old notes, but the
hashing stuff sticks in my memory. Maybe I misunderstood it. I did
find this on the 'net though:-

File Organization Techniques
Hashing - Each record is placed in the database at a location whose
address is computed as some function (hash function) of that record
(hash field). To store the record, the DBMS computes the hash address
for the new record and instructs the file manager to place the record at
that position


I worked on a database system many years ago on the HP3000 minicomputer
called IMAGE (later called TurboIMAGE) which used a hashing algorithm to
locate records in a table. This algorithm used only the primary key, not the
whole record, and produced a record number or address. As each record in a
table was fixed to the same length (variable length fields were not
supported, and empty fields were filled with null values) each record number
could be used to work out a physical address on the disk. A capacity was
defined for each table so that it could assign the space necessary to hold
that number of records. The capacity value could be changed, but this meant
rebuilding the table.

There were two approaches in this hashing algorithm:

(a) If the primary key was numeric and the key value was less than the
capacity of the table then the key values was used as the address.
Consequently each record had a fixed location in the table, and a sequential
read would retrieve the records in primary key sequence.

(b) If the primary key was numeric but greater in value than the capacity of
the table, or the primary key was not numeric, then the hashing algorithm
would produce an address somewhere else in the table space, and this address
would be used instead of the primary key value. The output from the hashing
algorithm was known as the "primary address".

(c) It was possible that different primary key values could hash to the same
"primary address", in which case the first record at that address was
allowed to stay at that address, but all others were moved to the nearest
available empty slots, known as a "secondary address". A chain of pointers
to these "secondary addresses" was maintained at the primary address.

(d) When performing a lookup using a primary key the hashing algorithm would
compute a primary address, and the DBMS would read the record at that
address. If the primary key of that record did not match the given primary
key then the DBMS would read down the chain of secondaries until it found
it, or would return "key not found".

(e) A problem arose when inserting a record whose primary key hashed to an
address which was already taken up by a "secondary". In this case the
secondary record would have to be moved to a different empty slot so that
the space could be used by the primary record, and the chain of secondary
addresses would have to be updated to reflect this movement. The insertion
of records could therefore be slowed down by this movement of secondary
records, which was given the term "migrating secondaries".

The whole point of this is to say that unless you know the inner workings of
your DBMS you cannot predict where each record will be written in the table
space, therefore you cannot predict the order in which records will be
presented to you when doing a sequential read. The only thing that you can
predict is that, barring deletions and insertions, the order will be exactly
the same whenever you perform another sequential read.

Here endeth the lesson.

--
Tony Marston
http://www.tonymarston.net



Jul 17 '05 #16

P: n/a
Tony Marston wrote:

[Great Big Snip}
The whole point of this is to say that unless you know the inner workings of
your DBMS you cannot predict where each record will be written in the table
space, therefore you cannot predict the order in which records will be
presented to you when doing a sequential read. The only thing that you can
predict is that, barring deletions and insertions, the order will be exactly
the same whenever you perform another sequential read.

Here endeth the lesson.


Sorry Tony, but in a modern commercial database that relies
heavily on caching of data you can't even rely on the ordering
being the same on subsequent sequential reads. In fact if the
data being read occupies more than one physical disk read unit
(for example 8 physical disk blocks) and all of the data being
read cannot be retained in RAM it is almost guaranteed that the
most recently read data from the first query will be the first
and not the last data read on a subsequent sequential query.

Jul 17 '05 #17

P: n/a
On Tue, 2 Mar 2004 22:20:25 -0500, "Agelmar" <if**********@comcast.net> wrote:
As for ordering records on a disk
using a hashing algorithm - I've never seen that before... I don't think you
would ever want to do that, either.


Oracle can do it, with hash clusters.

I think the idea is that since the location of the data is determined directly
by a hash of the primary key, retrieving by key is even quicker than an index
lookup - it's direct to the block, rather than going through several blocks
(probably three-ish) descending down an index tree structure.

Presumably the space requirements could get a bit heavy though, depending on
how the hash function works.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Jul 17 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.