Dear all,

I have a question about a query I would improve (I know it is not the best).

I have two tables:
USER:
DATA:
I think they are quiet self explaining.

When a user update something, instead of update the related record, he
insert a new one (this is for keeping track of the changes).

Now, if I want to extract the _last record for each user_ I select all
the users firstly (there are no duplicates in USER table) and, then, for
each user I select the corresponding data records, sorted by descending
timestamping: the first of the returning set is the desired one.

So, if I have n users, I need n+1 queries. How to improve that (no
subqueries, I'm stuck with MySql 4)?

Thank you

Libra

On Feb 9, 12:31 pm, Libra <libraramaANTIS... (AT) gmail (DOT) com> wrote:

NO you do not need n+1 queries. (And I especially don't understand
where you got the +1)

First of all define what is the "last record" because a relational DB
has only rows , not records, and it stores sets of data, there is no
internal order to the rows.

I'll assume your id on the DATA table is a sequence that only
increases with each insert. So my definition is the row in DATA for a
given users_id (why you made that plural is puzzling) with the largest
id value. to get what you seem to want, you need a join and a group by
something like this:

select USER.id, max (DATA.id )
from USER, DATA
where USER.id=DATA.users_id
group by USER.id;

make that a view and you will be all set.

oh wait, no subqueries (or views??)
can you do it in multiple stages?
create table current_user_data (
user_id USER ID TYPE,
data_id DATA ID TYPE,
primary key (user_id, data_id) );

INSERT INTO current_user_data SELECT USER.id, max (DATA.id )
from USER, DATA
where USER.id=DATA.users_id
group by USER.id ;

now it's a threeway join of USER, DATA, and CURRENT_USER_DATA
you update CURRENT_USER_DATA on any insert into DATA.

(All untested, use at your own risk)

ed
(not a mySQL 4 user)

Libra wrote:
I personally prefer putting history in a separate table rather than try to
sift through a bazillion time-separated copies in a transaction table.

I wonder what the best practice is. I do not know all ins and outs of the
arguments for the different approaches.

Given that MySQL is a free product, I am curious what the hindrance is to an
upgrade.

- Lew

Lew ha scritto:
A really simple one: the server is not mine :-)
Libra

Here is a "cut & paste" on your question:

Think raw data and single facts when designing a table. Let me use a
history table for price changes. The fact to store is that a price
had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;