My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

For example

Loan_ID    Rate    EffectiveDate
  1              3.3        2014/02/03
  1              2           2015/02/03
  2              5           2015/04/09
  2              5           2015/04/06

the query should return

Loan_ID    Rate    EffectiveDate
  1              2           2015/02/03
  2              5           2015/04/06

I've tried multiple queries but none that have worked with the parser.

select * from
(select Loan_Id, Max(effectiveDate) as EffectiveDate from InterestTerms
group by Loan_Id) as Y
join InterestTerms as X on Y.Loan_Id = X.Loan_Id and Y.EffectiveDate = X.EffectiveDate
order by Y.Loan_Id

select * from InterestTerms X
where EffectiveDate = (select max(effectiveDate) from interestTerms  Y where x.Loan_Id = y.Loan_Id)
order by Loan_Id

select
   X.*
FROM
   InterestTerms AS X
WHERE
   X.effectiveDate IN (
    select  
           Y.effectiveDate  
    from
         InterestTerms as Y
    WHERE
         Y.Loan_Id = X.Loan_Id
    ORDER BY
        Y.effectiveDate DESC     
   top 1
   )
order by
   X.Loan_Id

Boss wrote:

<<
My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

For example

Loan_ID    Rate    EffectiveDate
  1              3.3        2014/02/03
  1              2           2015/02/03
  2              5           2015/04/09
  2              5           2015/04/06

the query should return

Loan_ID    Rate    EffectiveDate
  1              2           2015/02/03
  2              5           2015/04/06
>>

You have to use 2 SQL statements or a script like below:

select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id
;
select * from InterestTerms X
inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);

Boss


I think this from a post by Adam H should point you in the right direction

Select Max(Date) as MaxDate, Customer
into Memory\Memory1
From Invoices
Group by Customer
;
Select Date, Invoice, Customer
From Invoices
Inner join Memory\M1 on (MaxDate=Date) and (Customer=Customer)

Essentially you use a script to create memory tables that are a substitute for the sub queries

Roy Lambert

Jose Eduardo Helminsky wrote:

Boss wrote:

<<
My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

For example

Loan_ID    Rate    EffectiveDate
  1              3.3        2014/02/03
  1              2           2015/02/03
  2              5           2015/04/09
  2              5           2015/04/06

the query should return

Loan_ID    Rate    EffectiveDate
  1              2           2015/02/03
  2              5           2015/04/06
>>

You have to use 2 SQL statements or a script like below:

select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id
;
select * from InterestTerms X
inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);

I'm not quite sure how I would divide it into two select statements. I'm using a DBISAM parser through visual studio.

var reader = new TEAReader();
     reader.Query(Query from above)

Boss wrote:

Jose Eduardo Helminsky wrote:

Boss wrote:

<<
My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

For example

Loan_ID    Rate    EffectiveDate
  1              3.3        2014/02/03
  1              2           2015/02/03
  2              5           2015/04/09
  2              5           2015/04/06

the query should return

Loan_ID    Rate    EffectiveDate
  1              2           2015/02/03
  2              5           2015/04/06
>>

You have to use 2 SQL statements or a script like below:

select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id
;
select * from InterestTerms X
inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);

I'm not quite sure how I would divide it into two select statements. I'm using a DBISAM parser through visual studio.

var reader = new TEAReader();
     reader.Query(Query from above)

Sorry forgot to post the error I got - [DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found Loan_Id in SELECT SQL statement

On 3/24/2015 10:44 AM, Boss wrote:
> My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
> I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

How about something like this

SELECT i1.*
FROM InterestTerms i1 LEFT JOIN InterestTerms i2
 ON (i1.loan_id = i2.loan_id AND i1.EffectiveDate < i2.EffectiveDate)
WHERE i2.loan_id IS NULL;


Raul

Raul wrote:

On 3/24/2015 10:44 AM, Boss wrote:
> My table has 4 columns,[Id, Loan_Id, Rate, EffectiveDate]
> I'm trying group by the loan_Id and grab the most recent record of that Loan_Id.

How about something like this

SELECT i1.*
FROM InterestTerms i1 LEFT JOIN InterestTerms i2
 ON (i1.loan_id = i2.loan_id AND i1.EffectiveDate < i2.EffectiveDate)
WHERE i2.loan_id IS NULL;


Raul

---

Hi Raul,

I tried your suggestion I got the following error DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found i1.loan_id in SELECT SQL

Boss



>select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id
>;
>select * from InterestTerms X
>inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);
>
>I'm not quite sure how I would divide it into two select statements. I'm using a DBISAM parser through visual studio.

As you have above. You separate each sql statement from the next by a semicolon


>Sorry forgot to post the error I got - [DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found Loan_Id in SELECT SQL statement

I'm pretty sure its this bit

inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);

you need to either specify an alias or specify memory each time so

inner join memory\m1 as tmp on (X.Loan_Id=tmp.Loan_Id and X.effectvedate=tmp.effectivedate);

should work

Roy Lambert

Roy Lambert wrote:

Boss



>select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id
>;
>select * from InterestTerms X
>inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);
>
>I'm not quite sure how I would divide it into two select statements. I'm using a DBISAM parser through visual studio.

As you have above. You separate each sql statement from the next by a semicolon


>Sorry forgot to post the error I got - [DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found Loan_Id in SELECT SQL statement

I'm pretty sure its this bit

inner join memory\m1 on (X.Loan_Id=m1.Loan_Id and X.effectvedate=m1.effectivedate);

you need to either specify an alias or specify memory each time so

inner join memory\m1 as tmp on (X.Loan_Id=tmp.Loan_Id and X.effectvedate=tmp.effectivedate);

should work

Roy Lambert


-----

Hi Roy,

I tried the follow and got this error [DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found Loan_Id in SELECT SQL statement

select Loan_Id, Max(effectiveDate) as EffectiveDate INTO memory\m1 from InterestTerms group by Loan_Id;
select * from InterestTerms X
inner join memory\m1 as tmp on (X.Loan_Id=tmp.Loan_Id and X.effectvedate=tmp.effectivedate);

On 3/24/2015 11:25 AM, Boss wrote:
> I tried your suggestion I got the following error DBISAM Engine Error # 11949 SQL parsing error - Expected column name but instead found i1.loan_id in SELECT SQL

This error just indicates that column name could not be matched - it's
simply a name parsing issue and not a SQL error per-se.

Double-check that your table name and alias name (i1 and i2) are ok in
the SQL.

Does it work when you run it in in DBSYS ?

What version of dbisam are you using (or odbs driver in your case
possibly i guess if you're on VS)?

Raul