Retrieving database record with the lowest value in field aaa?

[George Dainis]

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

[Florian Boldt]

Hi George,

how about:

select *
from ...
where somefield='somevalue'
and aaa=(select min(aaa) from ...)

or:

select aaa,...,...,...
from ...
where somefield='somevalues'
order by aaa
fetch first 1 row only

index on aaa would help in both cases
and don't use the asterisk ;)

hope that helps
cheers
Florian

George Dainis wrote:

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

[DA Morgan]

George Dainis wrote:

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 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
da******@x.washington.edu
(replace 'x' with 'u' to respond)