db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: selecting records in a derby database
Date Sun, 20 Oct 2013 13:57:56 GMT

On 20. okt. 2013, at 04:01, Bob M <rgmatthews@orcon.net.nz> wrote:

> hi
> 
> I have a derby database and a table with over 4,000 records............…

It is easier to think about a table as a hash table or tree, rather than an array. So there
isn't really a concept of first or last other than the ordering imposed by the primary key
(or other index). Meaning that there isn't an builtin way to retrieve the first or oldest
entry (or the newest) unless you have a column that reflects the age (could be an auto increment
column).

(Note that Derby is different from some other databases (e.g. Oracle) which have something
called row id which would have given you this ordering column automatically)
 
> 
> I wish to:- 
> 
> a) get the number of records in the table

SELECT COUNT(*) FROM t

> b) retrieve the last record

Assuming you have a column x which imposes the order you're after:

SELECT MAX(x) FROM t

to get the max key

SELECT * FROM t WHERE x=<max key>

> c) update this record

UPDATE t SET a=.., b=.. … WHERE x = <max key>

> d) add a new record and

INSERT INTO t VALUES(…)

> e) delete the first (oldest) record

DELETE FROM t WHERE x = (SELECT MIN(x) FROM t)

HTH,

Dyre
Mime
View raw message