Help learning SQL

Help learning SQL

Post by Richard Eva » Fri, 10 Oct 1997 04:00:00



I'm sort of teaching myself SQL and building some simple applications using
JDBC and an Access database.

But I've come across a type of query which I can't work out how to express
in SQL.

I have a large table, tab, with an integer fields x and y plus a whole load
of other data in each record.  I want to select the entire record which has
the minimum value of y for a given value of x.

Something like this pseudo-SQL ...

select * from tab where x = 42 and y = min(y)

Can this be done in a general way?

Thanks

Richard

--
-------------------------------------------------------------------------------
Richard Evans                                   Telephone : (+44) 1223 428200
Tadpole Technology plc                          Fax       : (+44) 1223 428203
Cambridge Science Park

Cambridge, CB4 4WQ, UK
-------------------------------------------------------------------------------

 
 
 

Help learning SQL

Post by Wim Goedefro » Fri, 10 Oct 1997 04:00:00




Quote:

> I have a large table, tab, with an integer fields x and y plus a whole
load
> of other data in each record.  I want to select the entire record which
has
> the minimum value of y for a given value of x.

> Something like this pseudo-SQL ...

> select * from tab where x = 42 and y = min(y)

> Can this be done in a general way?

What about this ?

SELECT * FROM tab
WHERE
  x= 42 AND
  y= (SELECT  MIN(y) FROM tab WHERE x=42)

without thanks :)

--
--
===================================================================
Goedefroy Wim                                      Vrije Universiteit
Brussel
Computer Science Department                                      STARLab
Pleinlaan 2      B-1050 Brussel       Belgium
phone : +32 2 629.37.50    fax : +32 2 629.35.25    office : 10G735

 
 
 

Help learning SQL

Post by Dirk Groble » Fri, 10 Oct 1997 04:00:00


--------------=_4D48010C6ED208047A24
Content-Description: filename="text1.txt"
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Sorry missing ( )
so use
select * from tab where x =3D 42 and y in (select min(y) from tab)

Dirk

Urspr=FCngliche Nachricht vom 10-9-97, 5:52:51 PM

Thema: Re: Help learning SQL

Hey,

the statement
select * from tab where x =3D 42 and y in select min(y) from tab

should work

Dirk

Quote:>>>>>>>>>>>>>>>>>> Urspr=FCngliche Nachricht <<<<<<<<<<<<<<<<<<


Thema Help learning SQL:

Quote:> I'm sort of teaching myself SQL and building some simple applications =

using

Quote:> JDBC and an Access database.
>=20
> But I've come across a type of query which I can't work out how to=20=

express
Quote:> in SQL.
>=20
> I have a large table, tab, with an integer fields x and y plus a whole=

=20
load
Quote:> of other data in each record.  I want to select the entire record=20
which has
> the minimum value of y for a given value of x.
>=20
> Something like this pseudo-SQL ...
>=20
> select * from tab where x =3D 42 and y =3D min(y)
>=20
> Can this be done in a general way?
>=20
> Thanks
>=20
> Richard
>=20
> --=20
>=20

-----------------------------------------------------------------------=

--------

Quote:> Richard Evans                                 Telephone : (+44) 1223=20=

428200
Quote:> Tadpole Technology plc                                Fax       :=20
(+44) 1223 428203
> Cambridge Science Park
> Milton Road                                   E-Mail    :=20


Quote:> Cambridge, CB4 4WQ, UK
>=20

-----------------------------------------------------------------------=

--------

Quote:>=20
>=20

--------------=_4D48010C6ED208047A24
Content-Description: filename="text1.html"
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
        <TITLE>Re: Help learning SQL</TITLE>
        <META NAME=3D"GENERATOR" CONTENT=3D"StarOffice/4.0 (WinNT/Win95)">
        <META NAME=3D"CREATED" CONTENT=3D"19971009;17513359">
        <META NAME=3D"CHANGEDBY" CONTENT=3D"Dirk Grobler">
        <META NAME=3D"CHANGED" CONTENT=3D"19971009;17592030">
</HEAD>
<BODY>
<P>Sorry missing ( )</P>
<P>so use</P>
<PRE>select * from tab where x =3D 42 and y in (select min(y) from tab)=

Dirk</PRE>
<P>
<BR><BR>
</P>
<HR>
<P ALIGN=3DLEFT>Urspr&uuml;ngliche Nachricht vom 10-9-97, 5:52:51 PM</P>=


<P ALIGN=3DLEFT>Thema: Re: Help learning SQL</P>
<HR>
<P><BR><BR>
</P>
<PRE>Hey,

the statement
select * from tab where x =3D 42 and y in select min(y) from tab

should work

Dirk

&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;=
 Urspr&uuml;ngliche Nachricht &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&l=
t;&lt;&lt;&lt;&lt;&lt;&lt;&lt;


hema Help learning SQL:

&gt; I'm sort of teaching myself SQL and building some simple applicatio=
ns using
&gt; JDBC and an Access database.
&gt;=20
&gt; But I've come across a type of query which I can't work out how to =
express
&gt; in SQL.
&gt;=20
&gt; I have a large table, tab, with an integer fields x and y plus a wh=
ole load
&gt; of other data in each record.  I want to select the entire record w=
hich has
&gt; the minimum value of y for a given value of x.
&gt;=20
&gt; Something like this pseudo-SQL ...
&gt;=20
&gt; select * from tab where x =3D 42 and y =3D min(y)
&gt;=20
&gt; Can this be done in a general way?
&gt;=20
&gt; Thanks
&gt;=20
&gt; Richard
&gt;=20
&gt; --=20
&gt; -------------------------------------------------------------------=
------------
&gt; Richard Evans                                 Telephone : (+44) 122=
3 428200
&gt; Tadpole Technology plc                                Fax       : (=
+44) 1223 428203
&gt; Cambridge Science Park

le.co.uk
&gt; Cambridge, CB4 4WQ, UK
&gt; -------------------------------------------------------------------=
------------
&gt;=20
&gt; </PRE>
</BODY>
</HTML>

--------------=_4D48010C6ED208047A24--

 
 
 

Help learning SQL

Post by Dirk Groble » Fri, 10 Oct 1997 04:00:00


--------------=_4D48010B688F0CB12584
Content-Description: filename="text1.txt"
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hey,

the statement
select * from tab where x =3D 42 and y in select min(y) from tab

should work

Dirk

Quote:>>>>>>>>>>>>>>>>>> Urspr=FCngliche Nachricht <<<<<<<<<<<<<<<<<<


Thema Help learning SQL:

Quote:> I'm sort of teaching myself SQL and building some simple applications =

using

Quote:> JDBC and an Access database.
>=20
> But I've come across a type of query which I can't work out how to=20=

express
Quote:> in SQL.
>=20
> I have a large table, tab, with an integer fields x and y plus a whole=

=20
load
Quote:> of other data in each record.  I want to select the entire record=20
which has
> the minimum value of y for a given value of x.
>=20
> Something like this pseudo-SQL ...
>=20
> select * from tab where x =3D 42 and y =3D min(y)
>=20
> Can this be done in a general way?
>=20
> Thanks
>=20
> Richard
>=20
> --=20
>=20

-----------------------------------------------------------------------=

--------

Quote:> Richard Evans                                 Telephone : (+44) 1223=20=

428200
Quote:> Tadpole Technology plc                                Fax       :=20
(+44) 1223 428203
> Cambridge Science Park
> Milton Road                                   E-Mail    :=20


Quote:> Cambridge, CB4 4WQ, UK
>=20

-----------------------------------------------------------------------=

--------

Quote:>=20
>=20

--------------=_4D48010B688F0CB12584
Content-Description: filename="text1.html"
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
        <TITLE>Re: Help learning SQL</TITLE>
        <META NAME=3D"GENERATOR" CONTENT=3D"StarOffice/4.0 (WinNT/Win95)">
        <META NAME=3D"CREATED" CONTENT=3D"19971009;17513359">
        <META NAME=3D"CHANGEDBY" CONTENT=3D"Dirk Grobler">
        <META NAME=3D"CHANGED" CONTENT=3D"19971009;17524907">
</HEAD>
<BODY>
<PRE>Hey,

the statement
select * from tab where x =3D 42 and y in select min(y) from tab

should work

Dirk

&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;=
 Urspr&uuml;ngliche Nachricht &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&l=
t;&lt;&lt;&lt;&lt;&lt;&lt;&lt;


hema Help learning SQL:

&gt; I'm sort of teaching myself SQL and building some simple applicatio=
ns using
&gt; JDBC and an Access database.
&gt;=20
&gt; But I've come across a type of query which I can't work out how to =
express
&gt; in SQL.
&gt;=20
&gt; I have a large table, tab, with an integer fields x and y plus a wh=
ole load
&gt; of other data in each record.  I want to select the entire record w=
hich has
&gt; the minimum value of y for a given value of x.
&gt;=20
&gt; Something like this pseudo-SQL ...
&gt;=20
&gt; select * from tab where x =3D 42 and y =3D min(y)
&gt;=20
&gt; Can this be done in a general way?
&gt;=20
&gt; Thanks
&gt;=20
&gt; Richard
&gt;=20
&gt; --=20
&gt; -------------------------------------------------------------------=
------------
&gt; Richard Evans                                 Telephone : (+44) 122=
3 428200
&gt; Tadpole Technology plc                                Fax       : (=
+44) 1223 428203
&gt; Cambridge Science Park

le.co.uk
&gt; Cambridge, CB4 4WQ, UK
&gt; -------------------------------------------------------------------=
------------
&gt;=20
&gt; </PRE>
</BODY>
</HTML>

--------------=_4D48010B688F0CB12584--