Retrieving database record with the lowest value in field aaa?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George Dainis - 27 Jan 2005 18:50 GMT

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

Reply to this Message

Florian Boldt - 27 Jan 2005 19:46 GMT

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

> 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)?
[quoted text clipped - 6 lines]
>
> George

Reply to this Message

DA Morgan - 27 Jan 2005 21:25 GMT

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.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Reply to this Message