Most Recent Date Query

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk, f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
           (Select max(f2.fmc_date) from fm_cost as f2
             where f2.fmc_terminal = f1.fmc_terminal
              and f2.fmc_vendor = f1.fmc_vendor
              and f2.fmc_prodlnk = f1.fmc_prodlnk
              GROUP by f.fmc_terminal, f.fmc_vendor, f.fmc_prodlnk)

if the statement isnt fast enough add (one) index for the triple terminal, vendor, prodlnk. drop all aother indexes if any.

Do you mean fmc_cost.fmc_terminal = f.fmc_vendor in line 5 ?

Should that be fmc_cost.fmc_terminal = f.fmc_terminal  ?

fluto
Yes I mean fm_cost.fmc_terminal = f.fmc_terminal  Sorry typo

js_profi

How is this different from what I am already running except for the alias on the first part?
I am still not getting results on this.

I have also just tried without success:

select fmc_terminal, fmc_vendor, fmc_prodlnk, fmc_date, fmc_time, fmc_cost
FROM fm_cost
WHERE fm_cost.fmc_date =
           (Select max(fmc_date) from fm_cost as f
             where fm_cost.fmc_terminal = f.fmc_terminal and
               fm_cost.fmc_vendor = f.fmc_vendor
              and fm_cost.fmc_prodlnk = f.fmc_prodlnk
              AND fm_cost.fmc_date = f.fmc_date
              AND fm_cost.fmc_time = f.fmc_time
              GROUP by f.fmc_terminal, f.fmc_vendor, f.fmc_prodlnk)

I did a quick import of this data and wrote a query that gives almost the results that you listed, missing one line though: 1003      71520016      2/15/2010      10:00      9      1.9725


SELECT      fm_cost.fmc_terminal,
            fm_cost.fmc_vendor,
            fm_cost.fmc_date,
            fm_cost.fmc_time,
            fm_cost.fmc_prodlnk,
            fm_cost.fmc_cost
FROM      fm_cost
            JOIN (
                  SELECT      fmc_terminal,
                              fmc_vendor,
                              MAX(fmc_date) AS fmc_date,
                              MAX(fmc_time) AS fmc_time
                  FROM      fm_cost
                  GROUP BY
                              fmc_terminal,
                              fmc_vendor
            ) AS x ON      x.fmc_terminal = fm_cost.fmc_terminal
                              AND x.fmc_vendor = fm_cost.fmc_vendor
                              AND x.fmc_date = fm_cost.fmc_date
                              AND x.fmc_time = fm_cost.fmc_time

Actually you don't need the GROUP BY do you ?

SELECT f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(SELECT max(f2.fmc_date) FROM fm_cost AS f2
WHERE f2.fmc_terminal = f1.fmc_terminal
AND f2.fmc_vendor = f1.fmc_vendor
AND f2.fmc_prodlnk = f1.fmc_prodlnk
);

nfashaw:  Informix SQL does not like "JOIN" I get a syntax error

fluto:  This query is the same as what I have already and it is giving me every record in the table. Both with and without the GROUP BY clause

do you have any records with differences in fmc_date and identical data in fmc_terminal, fmc_vendor and fmc_prodlnk?

try

SELECT f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
count(f1.fmc_date), max(f1.fmc_date), min(f1.fmc_date), max(f1.fmc_cost)
FROM fm_cost as f1
GROUP by f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk

that gives all the records where the combination terminal, vendor and prodlnk is unique and if the count(fmc_date) is 1 for all those your grouping criteria is wrong.

Hmmm ... something odd , my version works, it should work.

What happens if you just do

SELECT max(f2.fmc_date) FROM fm_cost AS f2
WHERE f2.fmc_terminal = 1003
AND f2.fmc_vendor = 46040025
AND f2.fmc_prodlnk = 9

You should get a single date "12/02/2010" (assuming your data is the same as the file you attached)

Is fmc_date a type DATE ? What does the schema of the table fm_cost look like ?

Do you have DBDATE set correctly?

js_profi:

The only duplicate will be on the time field. I will want to retrieve ALL the records for the Max date, so for example if there are two entries for same terminal, vendor and prodlnk on the same date they would vary by time, I do want to retrieve those in the query.

don't know if this helps but I am using dbaccess Version 7.24.UC7

fluto this:
SELECT f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(SELECT max(f2.fmc_date) FROM fm_cost AS f2
WHERE f2.fmc_terminal = f1.fmc_terminal
AND f2.fmc_vendor = f1.fmc_vendor
AND f2.fmc_prodlnk = f1.fmc_prodlnk
);
gives me every record in the table.

When I run the isolated select
SELECT max(f2.fmc_date) FROM fm_cost AS f2
WHERE f2.fmc_terminal = 1003
AND f2.fmc_vendor = 46040025
AND f2.fmc_prodlnk = 9
I do get the single correct date for that one record.

I am missing something simple here I know. Why is this not running properly? The join is not working somewhere.

I can make this work using a temp table as follows:

SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;

select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

However, I need to be able to run this and send results to Excel. I tried creating a stored procedure that inserts the records into the temp table first and then telling MSQuery to execute the stored procedure. It will not allow the creation of the temp table from the ODBC connection in MSQuery.

CREATE PROCEDURE fm_cost_maxdate ()
SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;
END PROCEDURE

EXECUTE PROCEDURE fm_cost_maxdate ();
select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

This also works fine. However, when I try to run this from the windows client I get the message that it cannot add the table t100.

This really should not be this hard to accomplish.

if the time is an additional criteria you need to add it to the date:

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk, f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE format(f1.fmc_date, "YYYYMMDD") &  format(f1.fmc_time, "hhmmss") =
           (Select max(format(f2.fmc_date, "YYYYMMDD") & format(f2.fmc_time, "hhmmss") )
            from fm_cost as f2
             where f2.fmc_terminal = f1.fmc_terminal
              and f2.fmc_vendor = f1.fmc_vendor
              and f2.fmc_prodlnk = f1.fmc_prodlnk)

     

As you say if the SELECT MAX(fmc_date) .... returns the correct dates then that is working fine.
So we have a simple join, effectively WHERE date = date that isn't working.

Could it be that there's a null date somewhere in the table ?

js_profi : The time is not the problem with this. THe query you are proposing is the same premise with the join as all the others above and is returning ALL records and then some with the combinations of cost and vendor, etc.

fluto: Why would a null date matter it would simply not be one of the records iselected by the filter.

See my query with the temp table above that DOES work correctly.

the query i posted would bring only those records with have maximum date + time for a given terminal, vendor, prodlnk. if that is all records your specification is wrong. can you post part of the results you get and mark those records which you dont want.

your query with the temp-table has a different group by clause than in the where clause of the subquery. if the results of your query were correct it either is by accident or cause the additional fields of the group by dont matter.

js_profi:

I will post the results of the queries here shortly.

The time is not the issues because if I have TWO records with the same MAX date but different times I WANT BOTH records in my results.

OK, it appears this is a performance issue on the server. I am getting good results on my smaple data with this query:

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(Select max(f2.fmc_date) from fm_cost as f2
where f2.fmc_terminal = f1.fmc_terminal
and f2.fmc_vendor = f1.fmc_vendor
and f2.fmc_prodlnk = f1.fmc_prodlnk
GROUP by f2.fmc_terminal, f2.fmc_vendor, f2.fmc_prodlnk)

However it runs for more than 30 minutes on the full database table and does not produce good results. I have a unique index on fmc_terminal, fmc_vendor, fmc_prodlnk, fmc_date, fmc_time

I also have a non-unique index on fmc_terminal, fmc_vendor, fmc_prodlnk

I can drop the non-unique index but not the unique index as that will effect the user interface and alow duplicates on the table.

How can I get this to perform better?
I am going to try dropping both indexes and recreating them to see if re-indexing the table helps.

I have dropped and recreated the index on the table. There is one unique index on terminal, vendor, prodlnk, date and time.

I also ran Update Statistics on the table.

I have relaunched the query and am determining how much time it is going to take to run. As of now it has beenrunning for almost 5 minutes. Prior to it ran for over an hour and still never finished. We will see if it ever finishes this time. Any other suggestions on getting this query to actually run on this table?

Thanks

OK here is what is working:

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(Select max(f2.fmc_date) from fm_cost as f2
where f2.fmc_terminal = f1.fmc_terminal
and f2.fmc_vendor = f1.fmc_vendor
and f2.fmc_prodlnk = f1.fmc_prodlnk
and f2.fmc_date >= (TODAY-10)
GROUP by f2.fmc_terminal, f2.fmc_vendor, f2.fmc_prodlnk)

There are 268,000 records in the table. With the additional date filter to look only at the last 10 days, it removes old records that are not needed. Anything with a date older than 10 days will not be a valid price anyway. This ran in about 5 minutes (FAR better than 30) - don't think performance is going to get much better on this query.

When I removed the Group by it saves about 30 seconds or so.

Thanks guys for all the help!

As soon as I figure out how to split the points up I will award them and close this one!

unique12u, the comment you accepted, posted a select statement that was merely a copy of my comment in http:#26581334.

Glad it's working.

;-)

I am SO HAPPY to find this solution!!  I've been struggling for days with this same table and the same need.  THANK YOU ALL!!!