SQL Query
Rob Kings
I have a problem with an SQL query. I'm not even sure that what I want to do
is achievable, and if it is, it's defeated me.
Access 97 or 2000. VB6 ADO.
I have 2 tables:
Identity which contains a key, and details such as name and address.
Chemistry which contains the key, and a date and physiological values
(Sodium, Potassium etc.)
The join between the 2 tables is on the key and is one to many. i.e. An
entry in the identity table (which relates to a person) can have many
corresponding entries in chemistry.
What I want to get is the last 5 chemistry entries (by date) for each
identity entry. But I can't do it.
Any suggestions. Is this even achievable in an SQL statement?
Cheers
Rob
Joe "Nuke Me Xemu" Foster
> I hope this is the right place for this sort of query.
Not as good as some of the database ngs, perhaps, but this'll do.
The quick and dirty way, using a correlated subquery:
select [identity].*, [chemistry].*
from [identity] inner join [chemistry]
on [identity].[key] = [chemistry].key
where [chemistry].[date] in
(select top 5 [c].[date]
from [chemistry] as [c]
where [c].[key] = [identity].[key]
order by [c].[date] desc)
Using "where [c].[key] = [chemistry].[key]" may be faster. YMMV.
There may be another way, using self-joins, but it won't look nice.
--
Joe Foster <mailto:jlfoster%40znet.com> KrazyKookKultz! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Jeff Johnson
> What I want to get is the last 5 chemistry entries (by date) for each
> identity entry. But I can't do it.
>
> Any suggestions. Is this even achievable in an SQL statement?
SQL is all about operations on sets. Until the bastardized "TOP n" clause
was added, SQL had no way of limiting a set to x rows as opposed to all of
them. Sure, there were DBMS-specific ways around it, such as the SET
ROWCOUNT statement in SQL Server, but PURE SQL has no way of doing this.
I'm not sure if TOP n is ANSI-standard, but Access and SQL Server do support
it, so it's there if you must use it.
Joe "Nuke Me Xemu" Foster
> "Rob Kings" <r...@nospam.vitalpulse.com> wrote in message
> news:eXlQGLxvBHA.2128@tkmsftngp07...
>
> > What I want to get is the last 5 chemistry entries (by date) for each
> > identity entry. But I can't do it.
> >
> > Any suggestions. Is this even achievable in an SQL statement?
>
> SQL is all about operations on sets. Until the bastardized "TOP n" clause
> was added, SQL had no way of limiting a set to x rows as opposed to all of
> them. Sure, there were DBMS-specific ways around it, such as the SET
> ROWCOUNT statement in SQL Server, but PURE SQL has no way of doing this.
select mytable.key, mytable.date, max(mytable.whatever) as whatever
from mytable inner join mytable as temp on mytable.key = temp.key
where temp.date >= mytable.date
group by mytable.key, mytable.date
having count(temp.date) <= 5
--
Joe Foster <mailto:jlfoster%40znet.com> On the cans? <http://www.xenu.net/>
Jarl Hermansson
>
> I'm not sure if TOP n is ANSI-standard, but Access and SQL Server do support
> it, so it's there if you must use it.
TOP n is not standard SQL. AFAIK standard SQL has no such function.
However many DBMS vendors have similar functions, like (RDB's?) SELECT
... ORDER BY ... LIMIT 100,10
If you want to verify if your SQL is standard compliant, check out the
SQL Validator. It recognizes SQL-92, SQL-99 and even the current draft
of SQL-200x:
http://developer.mimer.com/validator/index.tml
Regards,
Jarl
Rob Kings
Nice one. My SQL is pretty average at best, and whilst I'd got the "Top n"
syntax I couldn't get it to give me anything other than the 5 five results
ever. Your code works a treat.
Many thanks.
Rob
"Joe "Nuke Me Xemu" Foster" <j...@bftsi0.UUCP> wrote in message
news:OPR5YexvBHA.1540@tkmsftngp05...
Jeff Johnson
"Joe "Nuke Me Xemu" Foster" <j...@bftsi0.UUCP> wrote in message
> > SQL is all about operations on sets. Until the bastardized "TOP n"
clause
> > was added, SQL had no way of limiting a set to x rows as opposed to all
of
> > them. Sure, there were DBMS-specific ways around it, such as the SET
> > ROWCOUNT statement in SQL Server, but PURE SQL has no way of doing this.
>
> select mytable.key, mytable.date, max(mytable.whatever) as whatever
> from mytable inner join mytable as temp on mytable.key = temp.key
> where temp.date >= mytable.date
> group by mytable.key, mytable.date
> having count(temp.date) <= 5
Did you intend to do a SELF join in that statement?
Joe "Nuke Me Xemu" Foster
Yes, but perhaps you'd prefer instead,
select mytable.*
from mytable
where 5 <= (select count(*) from mytable as temp
where temp.key = mytable.key and temp.date >= mytable.date)
--
Joe Foster <mailto:jlfoster%40znet.com> Wanna buy a Bridge? <http://xenu.net/>