1

I have two tables:

CREATE TABLE fund_data
(
  id serial NOT NULL,
  fund_entries_id integer NOT NULL,
  fund_val numeric(25,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  CONSTRAINT fund_data_pkey PRIMARY KEY (id),
  CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
      REFERENCES fund_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id)
)

And:

CREATE TABLE fund_entries
(
  id serial NOT NULL,
  fiscal_prd_end_date date NOT NULL,
  company_id integer NOT NULL,
  ern_dt_id integer NOT NULL,
  CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
  CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
      REFERENCES ern_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
  CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id)
)

To get the most recent value from fund_data for a given company_id and bbg_pulls_id, I currently do two correlated sub-queries:

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Question: The query works find, but can I use windowing to avoid the two sub-queries? I've tried several times and I keep getting multiple rows.

EDIT: A description of the query in words: Find the max date for a given company_id, so I can find the id for that record. Then use that id to get the most recent value associated with that fk id (and max date) for a given bbg_pulls_id and company. Hopefully that helps.

  • Do you want ties? What happens if there are two md.max in the above example? – Evan Carroll Feb 1 '17 at 21:16
  • 1
    I should never get ties for a give company due to this constraint: CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date), – mountainclimber Feb 1 '17 at 21:21
  • Ahh, then there is a better way yet of doing this. – Evan Carroll Feb 1 '17 at 21:25
2

If I understand correctly:

SELECT DISTINCT
     last_value(fd.fund_val) OVER (ORDER BY fe.fiscal_prd_end_date)
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858

UPDATED:

Or just:

SELECT
     fd.fund_val
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858
ORDER BY fe.fiscal_prd_end_date DESC
LIMIT 1
  • That returns multiple rows. I just want one value. I added a description edit to my question. – mountainclimber Feb 1 '17 at 19:56
  • Updated variant? – Roman Tkachuk Feb 1 '17 at 19:59
  • isn't using order by, desc, and limit, bad practice and inefficient if you can avoid it? No opportunity for windowing huh? The second one does work though. – mountainclimber Feb 1 '17 at 20:00
  • For some casdes ORDER, LIMIT might be much better than windowing (especially for big indexed tables). Can you check EXPLAIN ANALYZE for your and updated query? – Roman Tkachuk Feb 1 '17 at 20:10
  • Yes. Yours is a bit faster! – mountainclimber Feb 1 '17 at 20:15
2
SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Seems to be the same thing as

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
        WHERE fe.fiscal_prd_end_date = (
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
        )
        AND fe.company_id = 858
     ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

We don't ever use implicit joins, so let's just rewrite that too..

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN (
        SELECT fe.id
        FROM fund_entries fe
        WHERE fe.fiscal_prd_end_date = (
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
        )
        AND fe.company_id = 858
) mi
  ON (fd.fund_entries_id = mi.id)
WHERE fd.bbg_pulls_id = 20;

We can clean that up a little more like this..

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe
  ON (fd.fund_entries_id = fe.id)
WHERE fe.fiscal_prd_end_date = (
    SELECT max(fe.fiscal_prd_end_date)
    FROM fund_entries fe
    WHERE fe.company_id = 858
)
AND fe.company_id = 858
AND fd.bbg_pulls_id = 20;

We can rewrite that where-clause too,

SELECT fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe1
  ON (fd.fund_entries_id = fe1.id)
INNER JOIN (
    SELECT fe.company_id, max(fe.fiscal_prd_end_date)
    FROM fund_entries fe
    GROUP BY company_id
) AS fe2
  ON (
    fe1.company_id = fe2.company_id
    AND fe1.fiscal_prd_end_date = fe2.max
  )
WHERE fe1.company_id = 858
AND fd.bbg_pulls_id = 20;

Update 1

In light of the fact that you can not have ties, this should be even faster using DISTINCT ON

SELECT DISTINCT ON ( company_id, fiscal_prd_end_date ) fd.fund_val
FROM fund_data fd
INNER JOIN fund_entries AS fe1
  ON (fd.fund_entries_id = fe1.id)
WHERE fe1.company_id = 858
AND fd.bbg_pulls_id = 20
ORDER BY company_id, fiscal_prd_end_date DESC;

Also, because you're only using one company, fe1.company_id = 858, you should be able to use a LIMIT here.. Which makes this exactly the same as what the other answer provided @Roman Tkachuk who wins it -- that method is even faster. You just were doing a lot of work before with your original query. It takes a lot of time to clean it up and figure out what you're needing. But, I agree with Roman, if you want just one company_id, the easy way is with a limit and one simple join.

  • 1
    Evan, thanks again! I'm still absorbing this, but your last query seems to have fe where it should have fe1... I get an error until I make a few of these changes. – mountainclimber Feb 1 '17 at 21:18
  • Whelp, oops my mistake =) – Evan Carroll Feb 1 '17 at 21:24
  • 1
    Roman's answer is all around better. I updated it again. It took me a while longer to figure out what you had and what you wanted. I went the method of reducing what you had -- that is to say, what you had was overly complex. But that was pretty irrelevant in this case as to what you wanted. – Evan Carroll Feb 1 '17 at 21:36
  • 1
    The iterations are informative thought! thank you! – mountainclimber Feb 1 '17 at 21:37
  • 1
    Yes. Understood. Everything I look at looks like a nail because I want to use the hammer. :) – mountainclimber Feb 1 '17 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.