Get the First record

[Rick]

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

[Pedro Graca]

Re: Get the First record

Rick wrote:[color=blue]
> 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.[/color]

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 =--

[Tony Marston]

Re: Get the First record

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/mysql/bychapter/manual_SQL_Syntax.html#SELECT

--
Tony Marston

http://www.tonymarston.net

"Rick" <RB@newsgroup.n et> wrote in message
news:11Y0c.9775 8$Xp.432840@att bi_s54...[color=blue]
> Hi all,
>
> Is there a MySQL function to get the first record through a query? I would[/color]
like to open a page of[color=blue]
> client records with the first one shown. The other records can be[/color]
accessed from a hyperlinked list.[color=blue]
>
> Thanks for any advice,
>
> Rick[/color]

[Geoff Berrow]

Re: Get the First record

I noticed that Message-ID: <11Y0c.97758$Xp .432840@attbi_s 54> from Rick
contained the following:
[color=blue]
>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.[/color]

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("SE LECT * FROM table ORDER BY id ");
//default is ascending

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

//loop through rest of records
while($myrow = mysql_fetch_arr ay($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/

[Agelmar]

Re: Get the First record

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID: <11Y0c.97758$Xp .432840@attbi_s 54> from Rick
> contained the following:
>[color=green]
>> 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.[/color]
>
> Data is not stored in any particular order in a database. Therefore
> the
> concept of 'first record' is meaningless.[/color]

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.

[michel]

Re: Get the First record

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" <ifetteNOSPAM@c omcast.net> wrote in message
news:c220o9$1o3 8nl$1@ID-30799.news.uni-berlin.de...[color=blue]
> Geoff Berrow wrote:[color=green]
> > I noticed that Message-ID: <11Y0c.97758$Xp .432840@attbi_s 54> from Rick
> > contained the following:
> >[color=darkred]
> >> 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.[/color]
> >
> > Data is not stored in any particular order in a database. Therefore
> > the
> > concept of 'first record' is meaningless.[/color]
>
> Actually, this is not always true... While data may be stored unordered,[/color]
the[color=blue]
> data may also be stored in an ordered manner, usually in the order defined
> by an index on the relation.
>
>[/color]

[Pedro Graca]

Re: Get the First record

Agelmar wrote:[color=blue]
> 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.[/color]

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 =--

[Rick]

Re: Get the First record

Pedro Graca wrote:
[color=blue]
> Rick wrote:
>[color=green]
>>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.[/color]
>
>
> 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[/color]

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

[Geoff Berrow]

Re: Get the First record

I noticed that Message-ID: <JB41c.166553$u V3.715478@attbi _s51> from Rick
contained the following:
[color=blue]
>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.[/color]

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 ?[color=blue]
>
>The 'Limit' clause as suggested by both Pedro and Tony appears to be what I want.[/color]

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/

[Agelmar]

Re: Get the First record

Pedro Graca wrote:[color=blue]
> Agelmar wrote:[color=green]
>> 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.[/color]
>
> The key word there is *may*.[/color]
<snip>
No arguments. I was just responding to the specific sentence "Data is not
stored in any particular order in a database."

[Geoff Berrow]

Re: Get the First record

I noticed that Message-ID: <c22qv1$1mfu0h$ 1@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
[color=blue][color=green]
>> The key word there is *may*.[/color]
><snip>
>No arguments. I was just responding to the specific sentence "Data is not
>stored in any particular order in a database."[/color]

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/

[Agelmar]

Re: Get the First record

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID:
> <c22qv1$1mfu0h$ 1@ID-30799.news.uni-berlin.de> from Agelmar contained
> the following:
>[color=green][color=darkred]
>>> The key word there is *may*.[/color]
>> <snip>
>> No arguments. I was just responding to the specific sentence "Data
>> is not stored in any particular order in a database."[/color]
>
> Which is /still/ true.[/color]

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.

[Geoff Berrow]

Re: Get the First record

I noticed that Message-ID: <c23fd4$1og85s$ 1@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
[color=blue]
>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.[/color]

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/

[Agelmar]

Re: Get the First record

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID:
> <c23fd4$1og85s$ 1@ID-30799.news.uni-berlin.de> from Agelmar contained
> the following:
>[color=green]
>> 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.[/color]
>
> 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.[/color]

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)

[Geoff Berrow]

Re: Get the First record

I noticed that Message-ID: <c23itq$1ngrqm$ 1@ID-30799.news.uni-berlin.de>
from Agelmar contained the following:
[color=blue]
>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)[/color]

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/