Retrieving database record with the lowest value in field aaa?

How do I code a SQL SELECT statement so that always only this record is retr
ieved
which matches a certain criteria AND has the lowest ID (= value in key field
aaa)?
It must me something like
SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa)
)
As a result either zero or at most 1 record should be passed back.
George"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
I am not exactly sure what you are looking for, but typically this done with
a subselect:
SELECT a.col1, b.col2 FROM table-name a
WHERE a.col1 = 'somevalue'
AND a.col2 = (select b.min(col2) from FROM table-name b where ...)|||You can use TOP clause.
SELECT top 1 * FROM ... WHERE somefield='somevalue'
order by aaa asc
AMB
"George Dainis" wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>|||This would appear to be a good candidate for an inline view using
rownum.
Something like;
Select ...<information you want to see>
from (Select ...<information you want to see>
_ from ...
_ where somefield='somevalue'
_ order by aaa)
where rownum < 2;
(Ignore the underscore, they're just there for holding the indentation)|||George Dainis wrote:
> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
>
Lookup min() (in your textbook?)
Regards,
Frank van Bortel|||try
ROW_NUMBER() OVER(order by ...)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
> As a result either zero or at most 1 record should be passed back.
> George
>
AND aaa = ( <use a subquery with the MIN() function> )
++ mcs|||George Dainis wrote:

> How do I code a SQL SELECT statement so that always only this record is re
trieved
> which matches a certain criteria AND has the lowest ID (= value in key fie
ld aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aa
a))
> As a result either zero or at most 1 record should be passed back.
> George
I am going to assume, given that you have posted this to every usenet
group you can spell, among them comp.databases.oracle.misc,
microsoft.public.sqlserver.programming, comp.databases.oracle, and
comp.databases.ibm-db2, that you are trying to find someone to do your
homework for you.
The optimal solution will vary by product and even version so posting
as you have says something about what you are trying to do.
As we don't do other people's homework for them and you seemingly have
made no attempt to solve this on your own ... go talk to your faculty
advisor about what you have done and ask for help there.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:ctbd5d$sq3$00$1@.news.t-online.com...
> How do I code a SQL SELECT statement so that always only this record is
> retrieved
> which matches a certain criteria AND has the lowest ID (= value in key
> field aaa)?
> It must me something like
> SELECT * FROM ... WHERE somefield='somevalue' AND
> aaa=lowestkey(column(aaa))
lookup MIN in the sql reference.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com|||On Thu, 27 Jan 2005 19:50:22 +0100, george.dainis@.bluecorner.com (George
Dainis) wrote:

>How do I code a SQL SELECT statement so that always only this record is ret
rieved
>which matches a certain criteria AND has the lowest ID (= value in key fiel
d aaa)?
>It must me something like
>SELECT * FROM ... WHERE somefield='somevalue' AND aaa=lowestkey(column(aaa
))
>As a result either zero or at most 1 record should be passed back.
SELECT *
FROM ...
WHERE somefield = 'somevalue'
AND aaa = (SELECT MIN(aaa)
FROM ...
WHERE somefield = 'somevalue')
Andy Hassall / <andy@.andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool