Hi

I am trying to retrieve values from the last record in a dataset, but
not having much luck.  I could do it by two consecutive queries, using
max or lastautoinc and then searching for that value in the second
query, but I thought it should be to be done in one query.

Any ideas please?

cheers

paul

Select value from tablea
where value in (select min(value) from tablea)

maybe?.


"PB" <pb2525@myrealbox.com> wrote in message
news:E96C4A65-3814-4C06-B51F-45BE1C397220@news.elevatesoft.com...
> Hi
>
> I am trying to retrieve values from the last record in a dataset, but not
> having much luck.  I could do it by two consecutive queries, using max or
> lastautoinc and then searching for that value in the second query, but I
> thought it should be to be done in one query.
>
> Any ideas please?
>
> cheers
>
> paul

Thanks Clive, works like a charm, I have thought about that one but
rejected it because of the following statement in the manual :

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Subqueries are supported in the WHERE clause. A subquery works like a
search condition to restrict the number of rows returned by the outer,
or "parent" query. Such subqueries must be valid SELECT statements.
SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
to columns in the outer (or"parent") statement.  In the following
statement, the subquery is said to be un-correlated:

SELECT *
FROM "Clients.dat" C
WHERE C.Acct_Nbr IN
(SELECT H.Acct_Nbr
FROM "Holdings.dat" H
WHERE H.Pur_Date BETWEEN "1994-01-01" AND "1994-12-31")
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I have tested

Select *
from flights
where FlightNo = (select max(flightNo) from flights)

which did not work.  I have used = as I am expecting only one value
back.  But DBIASM will not execute it.  Given the statement in the
manual, I have not tried again.  So I am not sure why the following
statement works, but it does.  I am using v3.30.


Select *
from flights
where FlightNo in (select max(flightNo) from flights)

Anyway, thanks again

paul



Clive wrote:
> Select value from tablea
> where value in (select min(value) from tablea)
>
> maybe?.
>
>
> "PB" <pb2525@myrealbox.com> wrote in message
> news:E96C4A65-3814-4C06-B51F-45BE1C397220@news.elevatesoft.com...
>
>>Hi
>>
>>I am trying to retrieve values from the last record in a dataset, but not
>>having much luck.  I could do it by two consecutive queries, using max or
>>lastautoinc and then searching for that value in the second query, but I
>>thought it should be to be done in one query.
>>
>>Any ideas please?
>>
>>cheers
>>
>>paul
>
>
>

You cannot use = with subqueries as subqueries can produce more than 1
value, using IN has the same effect as your list only contains 1 value, so
is the same in effect as =

Sub queries are mean to produce result sets just as regular queries, so by
saying IN the parent query searches the sub-query for that value, now if
your sub-query only produces 1 row as in your case, it doesnt have to search
for long.

Correlated sub-queries where you can reference columns from the parent query
in the sub-query are on my wish list.


"PB" <pb2525@myrealbox.com> wrote in message
news:F2D63337-88C4-4DC4-8A36-AA419CD045F4@news.elevatesoft.com...
> Thanks Clive, works like a charm, I have thought about that one but
> rejected it because of the following statement in the manual :
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Subqueries are supported in the WHERE clause. A subquery works like a
> search condition to restrict the number of rows returned by the outer, or
> "parent" query. Such subqueries must be valid SELECT statements.
> SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
> to columns in the outer (or"parent") statement.  In the following
> statement, the subquery is said to be un-correlated:
>
> SELECT *
> FROM "Clients.dat" C
> WHERE C.Acct_Nbr IN
> (SELECT H.Acct_Nbr
> FROM "Holdings.dat" H
> WHERE H.Pur_Date BETWEEN "1994-01-01" AND "1994-12-31")
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> I have tested
>
> Select *
> from flights
> where FlightNo = (select max(flightNo) from flights)
>
> which did not work.  I have used = as I am expecting only one value back.
> But DBIASM will not execute it.  Given the statement in the manual, I have
> not tried again.  So I am not sure why the following statement works, but
> it does.  I am using v3.30.
>
>
> Select *
> from flights
> where FlightNo in (select max(flightNo) from flights)
>
> Anyway, thanks again
>
> paul
>
>
>
> Clive wrote:
>> Select value from tablea
>> where value in (select min(value) from tablea)
>>
>> maybe?.
>>
>>
>> "PB" <pb2525@myrealbox.com> wrote in message
>> news:E96C4A65-3814-4C06-B51F-45BE1C397220@news.elevatesoft.com...
>>
>>>Hi
>>>
>>>I am trying to retrieve values from the last record in a dataset, but not
>>>having much luck.  I could do it by two consecutive queries, using max or
>>>lastautoinc and then searching for that value in the second query, but I
>>>thought it should be to be done in one query.
>>>
>>>Any ideas please?
>>>
>>>cheers
>>>
>>>paul
>>
>>

Thanks again Clive, clearly I have misunderstood the correlated query
concept in DBIASM.  If you could clarify I the following, I would
appreciated it.

1) In the syntax where I have first learned about subqueries you would
use = if you expected just one value in the result set (min, max etc.)
and IN if you expected more then one value in the result set.  It would
seem that DBIASM only accepts IN.  It does not seem to be stated
explicitly in the manual, so I would have thought that logical
comparison operators could be used.

2) It would be my understanding (clearly wrong) that my query

Select *
from flights
where FlightNo = (select max(flightNo) from flights)

is referring to columns in the parent query, as the flightNo column
would be part of the result set of the parent query.

A bit of a bummer really, because I have used various kludges in the
past to get around this, as I have assumed the above query was illegal.


cheers

Paul


Clive wrote:
> You cannot use = with subqueries as subqueries can produce more than 1
> value, using IN has the same effect as your list only contains 1 value, so
> is the same in effect as =
>
> Sub queries are mean to produce result sets just as regular queries, so by
> saying IN the parent query searches the sub-query for that value, now if
> your sub-query only produces 1 row as in your case, it doesnt have to search
> for long.
>
> Correlated sub-queries where you can reference columns from the parent query
> in the sub-query are on my wish list.
>
>
> "PB" <pb2525@myrealbox.com> wrote in message
> news:F2D63337-88C4-4DC4-8A36-AA419CD045F4@news.elevatesoft.com...
>
>>Thanks Clive, works like a charm, I have thought about that one but
>>rejected it because of the following statement in the manual :
>>
>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>Subqueries are supported in the WHERE clause. A subquery works like a
>>search condition to restrict the number of rows returned by the outer, or
>>"parent" query. Such subqueries must be valid SELECT statements.
>>SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
>>to columns in the outer (or"parent") statement.  In the following
>>statement, the subquery is said to be un-correlated:
>>
>>SELECT *
>>FROM "Clients.dat" C
>>WHERE C.Acct_Nbr IN
>>(SELECT H.Acct_Nbr
>>FROM "Holdings.dat" H
>>WHERE H.Pur_Date BETWEEN "1994-01-01" AND "1994-12-31")
>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>>I have tested
>>
>>Select *
>>from flights
>>where FlightNo = (select max(flightNo) from flights)
>>
>>which did not work.  I have used = as I am expecting only one value back.
>>But DBIASM will not execute it.  Given the statement in the manual, I have
>>not tried again.  So I am not sure why the following statement works, but
>>it does.  I am using v3.30.
>>
>>
>>Select *
>>from flights
>>where FlightNo in (select max(flightNo) from flights)
>>
>>Anyway, thanks again
>>
>>paul
>>
>>
>>
>>Clive wrote:
>>
>>>Select value from tablea
>>>where value in (select min(value) from tablea)
>>>
>>>maybe?.
>>>
>>>
>>>"PB" <pb2525@myrealbox.com> wrote in message
>>>news:E96C4A65-3814-4C06-B51F-45BE1C397220@news.elevatesoft.com...
>>>
>>>
>>>>Hi
>>>>
>>>>I am trying to retrieve values from the last record in a dataset, but not
>>>>having much luck.  I could do it by two consecutive queries, using max or
>>>>lastautoinc and then searching for that value in the second query, but I
>>>>thought it should be to be done in one query.
>>>>
>>>>Any ideas please?
>>>>
>>>>cheers
>>>>
>>>>paul
>>>
>>>
>

1)
No, you cant use equals at all with subqueries.

Select *
from flights
where FlightNo = (select max(flightNo) from flights)

is not valid syntax in DBISAM

Select *
from flights
where FlightNo IN (select max(flightNo) from flights)

Will produce the results you want, You can only use subqueries with the IN
operator, as your subquery could produce zero, 1 or many rows.

2)

The flightno in your subquery is produced as a result of THAT subquery which
although uses the same table as the parent query is a different query
alltogther, DBISAM knows this.
The subquery can be whatever you like, it doesnt have to be related to the
parent query in any way. The column names dont even need to match.

For example this query, would return you all Flights where this flightNo was
in internationalflights table for example. (For lurkers I know you would
generally use a join in this instance, so dont call me on it!)
The point im making is that the subquery does not need to be related in
anyway to the parent query (and can not be), its just used to produce a list
of results that the parent query can see if its value is IN.

Select *
from flights
where FlightNo IN (select flight from internationalflights)


So this query

Select *
from flights
where FlightNo IN (select max(flightNo) from flights)

is in effect 2 queries, the first (Parent) will select all records from
table flights where FlightNo is IN the result set of the second query which
returns the Max(FlighNo) from flights

Now as Max(FlightNO) can only produce 1 row, the parent query will only
return rows where FlightNo equals the value from this row, so you should
only get 1 row.

Make Sense?.


Is the correct syntax to achieve what you want

"PB" <pb2525@myrealbox.com> wrote in message
news:30EC2277-5B5B-45B9-A9A4-BD4BC81D1C57@news.elevatesoft.com...
> Thanks again Clive, clearly I have misunderstood the correlated query
> concept in DBIASM.  If you could clarify I the following, I would
> appreciated it.
>
> 1) In the syntax where I have first learned about subqueries you would use
> = if you expected just one value in the result set (min, max etc.) and IN
> if you expected more then one value in the result set.  It would seem that
> DBIASM only accepts IN.  It does not seem to be stated explicitly in the
> manual, so I would have thought that logical comparison operators could be
> used.
>
> 2) It would be my understanding (clearly wrong) that my query
>
> Select *
> from flights
> where FlightNo = (select max(flightNo) from flights)
>
> is referring to columns in the parent query, as the flightNo column would
> be part of the result set of the parent query.
>
> A bit of a bummer really, because I have used various kludges in the past
> to get around this, as I have assumed the above query was illegal.
>
>
> cheers
>
> Paul
>
>
> Clive wrote:
>> You cannot use = with subqueries as subqueries can produce more than 1
>> value, using IN has the same effect as your list only contains 1 value,
>> so is the same in effect as =
>>
>> Sub queries are mean to produce result sets just as regular queries, so
>> by saying IN the parent query searches the sub-query for that value, now
>> if your sub-query only produces 1 row as in your case, it doesnt have to
>> search for long.
>>
>> Correlated sub-queries where you can reference columns from the parent
>> query in the sub-query are on my wish list.
>>
>>
>> "PB" <pb2525@myrealbox.com> wrote in message
>> news:F2D63337-88C4-4DC4-8A36-AA419CD045F4@news.elevatesoft.com...
>>
>>>Thanks Clive, works like a charm, I have thought about that one but
>>>rejected it because of the following statement in the manual :
>>>
>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>Subqueries are supported in the WHERE clause. A subquery works like a
>>>search condition to restrict the number of rows returned by the outer, or
>>>"parent" query. Such subqueries must be valid SELECT statements.
>>>SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
>>>to columns in the outer (or"parent") statement.  In the following
>>>statement, the subquery is said to be un-correlated:
>>>
>>>SELECT *
>>>FROM "Clients.dat" C
>>>WHERE C.Acct_Nbr IN
>>>(SELECT H.Acct_Nbr
>>>FROM "Holdings.dat" H
>>>WHERE H.Pur_Date BETWEEN "1994-01-01" AND "1994-12-31")
>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>
>>>I have tested
>>>
>>>Select *
>>>from flights
>>>where FlightNo = (select max(flightNo) from flights)
>>>
>>>which did not work.  I have used = as I am expecting only one value back.
>>>But DBIASM will not execute it.  Given the statement in the manual, I
>>>have not tried again.  So I am not sure why the following statement
>>>works, but it does.  I am using v3.30.
>>>
>>>
>>>Select *
>>>from flights
>>>where FlightNo in (select max(flightNo) from flights)
>>>
>>>Anyway, thanks again
>>>
>>>paul
>>>
>>>
>>>
>>>Clive wrote:
>>>
>>>>Select value from tablea
>>>>where value in (select min(value) from tablea)
>>>>
>>>>maybe?.
>>>>
>>>>
>>>>"PB" <pb2525@myrealbox.com> wrote in message
>>>>news:E96C4A65-3814-4C06-B51F-45BE1C397220@news.elevatesoft.com...
>>>>
>>>>
>>>>>Hi
>>>>>
>>>>>I am trying to retrieve values from the last record in a dataset, but
>>>>>not having much luck.  I could do it by two consecutive queries, using
>>>>>max or lastautoinc and then searching for that value in the second
>>>>>query, but I thought it should be to be done in one query.
>>>>>
>>>>>Any ideas please?
>>>>>
>>>>>cheers
>>>>>
>>>>>paul
>>>>
>>>>
>>

Clive wrote:
> 1)
> No, you cant use equals at all with subqueries.

Understand, obviously a shortcoming of DBISAM query engine.  Other SQL
dialects allow the use of other comparison operators such as =, <, >
etc. where it is up to the programmer to ensure that a single value is
produced by the subquery.

It is the part that has me stumped
> 2)
>
> The flightno in your subquery is produced as a result of THAT subquery which
> although uses the same table as the parent query is a different query
> alltogther, DBISAM knows this.
> The subquery can be whatever you like, it doesnt have to be related to the
> parent query in any way. The column names dont even need to match.

Snip

>
> is in effect 2 queries, the first (Parent) will select all records from
> table flights where FlightNo is IN the result set of the second query which
> returns the Max(FlighNo) from flights
>
> Now as Max(FlightNO) can only produce 1 row, the parent query will only
> return rows where FlightNo equals the value from this row, so you should
> only get 1 row.
>
> Make Sense?.

Sure this part does, I think my problem is figuring out what is
correlated and what is not.  I worked on the following statement from
the manual:

"SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot
refer to columns in the outer (or"parent") statement."

Which to me implied, that if a column is mentioned in the parent table,
it cannot be used in the subquery.

So, am I right to think that the subqueries can refer to the columns in
the table quired (assume it is the same table), but cannot refer to the
result column *generated* in the parent query?

cheers

Paul

Yes, Sybase, SQL Server etc all allow equals, but DBISAM doesnt..

yes correct regarding the correlated stuff, the Subquery can reference any
columns it likes from its own result set, BUT not the parent result set.

Generally the way around this is to specify the same arguments or whatever
in the subquery to get the same result, of course depends on what you are
trying to achieve.


"PB" <pb2525@myrealbox.com> wrote in message
news:11113956-54D1-49DE-941E-FB616C9234F6@news.elevatesoft.com...
> Clive wrote:
>> 1)
>> No, you cant use equals at all with subqueries.
>
> Understand, obviously a shortcoming of DBISAM query engine.  Other SQL
> dialects allow the use of other comparison operators such as =, <, > etc.
> where it is up to the programmer to ensure that a single value is produced
> by the subquery.
>
> It is the part that has me stumped
>> 2)
>>
>> The flightno in your subquery is produced as a result of THAT subquery
>> which although uses the same table as the parent query is a different
>> query alltogther, DBISAM knows this.
>> The subquery can be whatever you like, it doesnt have to be related to
>> the parent query in any way. The column names dont even need to match.
>
> Snip
>
>>
>> is in effect 2 queries, the first (Parent) will select all records from
>> table flights where FlightNo is IN the result set of the second query
>> which returns the Max(FlighNo) from flights
>>
>> Now as Max(FlightNO) can only produce 1 row, the parent query will only
>> return rows where FlightNo equals the value from this row, so you should
>> only get 1 row.
>>
>> Make Sense?.
>
> Sure this part does, I think my problem is figuring out what is correlated
> and what is not.  I worked on the following statement from the manual:
>
> "SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
> to columns in the outer (or"parent") statement."
>
> Which to me implied, that if a column is mentioned in the parent table, it
> cannot be used in the subquery.
>
> So, am I right to think that the subqueries can refer to the columns in
> the table quired (assume it is the same table), but cannot refer to the
> result column *generated* in the parent query?
>
> cheers
>
> Paul
>

Great, I think I got it, thanks for your patience Clive.

Paul

Clive wrote:
> Yes, Sybase, SQL Server etc all allow equals, but DBISAM doesnt..
>
> yes correct regarding the correlated stuff, the Subquery can reference any
> columns it likes from its own result set, BUT not the parent result set.
>
> Generally the way around this is to specify the same arguments or whatever
> in the subquery to get the same result, of course depends on what you are
> trying to achieve.
>
>
> "PB" <pb2525@myrealbox.com> wrote in message
> news:11113956-54D1-49DE-941E-FB616C9234F6@news.elevatesoft.com...
>
>>Clive wrote:
>>
>>>1)
>>>No, you cant use equals at all with subqueries.
>>
>>Understand, obviously a shortcoming of DBISAM query engine.  Other SQL
>>dialects allow the use of other comparison operators such as =, <, > etc.
>>where it is up to the programmer to ensure that a single value is produced
>>by the subquery.
>>
>>It is the part that has me stumped
>>
>>>2)
>>>
>>>The flightno in your subquery is produced as a result of THAT subquery
>>>which although uses the same table as the parent query is a different
>>>query alltogther, DBISAM knows this.
>>>The subquery can be whatever you like, it doesnt have to be related to
>>>the parent query in any way. The column names dont even need to match.
>>
>>Snip
>>
>>
>>>is in effect 2 queries, the first (Parent) will select all records from
>>>table flights where FlightNo is IN the result set of the second query
>>>which returns the Max(FlighNo) from flights
>>>
>>>Now as Max(FlightNO) can only produce 1 row, the parent query will only
>>>return rows where FlightNo equals the value from this row, so you should
>>>only get 1 row.
>>>
>>>Make Sense?.
>>
>>Sure this part does, I think my problem is figuring out what is correlated
>>and what is not.  I worked on the following statement from the manual:
>>
>>"SELECT subqueries cannot be correlated in DBISAM, i.e. they cannot refer
>>to columns in the outer (or"parent") statement."
>>
>>Which to me implied, that if a column is mentioned in the parent table, it
>>cannot be used in the subquery.
>>
>>So, am I right to think that the subqueries can refer to the columns in
>>the table quired (assume it is the same table), but cannot refer to the
>>result column *generated* in the parent query?
>>
>>cheers
>>
>>Paul
>>
>
>
>