CREATE TABLE _jant
(
  id serial NOT NULL,
  article text,
  name text,
  fav boolean DEFAULT false,
  CONSTRAINT _jant_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE _jant
  OWNER TO postgres;
GRANT ALL ON TABLE _jant TO public;
GRANT ALL ON TABLE _jant TO postgres;

CREATE INDEX _jant_article_idx
  ON _jant
  USING btree
  (article COLLATE pg_catalog."default");

CREATE INDEX _jant_name_idx
  ON _jant
  USING btree
  (name COLLATE pg_catalog."default");

CREATE TABLE _jant_d
(
  id serial NOT NULL,
  id_article integer,
  pricedate date,
  cost text,
  CONSTRAINT _jant_d_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE _jant_d
  OWNER TO postgres;
GRANT ALL ON TABLE _jant_d TO public;
GRANT ALL ON TABLE _jant_d TO postgres;

CREATE INDEX _jant_d_id_article_pricedate_idx
  ON _jant_d
  USING btree
  (id_article, pricedate);

CREATE INDEX _jant_d_pricedate_idx
  ON _jant_d
  USING btree
  (pricedate);

CREATE INDEX d_jant_d_id_article_idx
  ON _jant_d
  USING btree
  (id_article);

CREATE TABLE _jant_cross1
(
  id serial NOT NULL,
  id_for_detail integer,
  shop text,
  id_in_shop integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE _jant_cross1
  OWNER TO postgres;
GRANT ALL ON TABLE _jant_cross1 TO public;
GRANT ALL ON TABLE _jant_cross1 TO postgres;

CREATE INDEX "IDIDFD"
  ON _jant_cross1
  USING btree
  (id_for_detail);

CREATE INDEX id
  ON _jant_cross1
  USING btree
  (id);

SELECT a.id, a.article, a.name, b.cost
FROM  _jant  a
LEFT JOIN _jant_d b ON b.id_article = a.id AND b.pricedate=(SELECT MAX(pricedate) FROM _jant_d LIMIT 1)
WHERE (b.cost IS NOT NULL)
ORDER BY a.name

select _jant.name, COUNT(*)
FROM _jant_cross1 
INNER JOIN _jant ON _jant.id= _jant_cross1.id_for_detail
GROUP BY _jant.id