CODEWITHSUNDEEP
phpmysqlgreatest-n-per-group
Samia Ruponti
Samia Ruponti

Reputation: 4038

mysql fetch all row based on join query and latest date time

Hello I have two tables:

table1 - eod_stock

company_code | open | high | low | ltp | close | ycp | total_volume | total_value | datetime

table 2 - company

ID | code | name

here company code = code so to get all name and other info i used this code:

but first one gives me error and 2nd one returns only one row, but i need all 200 companies with their associated info.

select
  company.code,
  company.name,
  eod_stock.open,
  eod_stock.high,
  max(eod_stock.datetime)
from 
  company
  right join company on company.code= eod_stock.company_code;

and 

select
  eod_stock.company_code,
  max(eod_stock.close),
  eod_stock.total_volume,
  eod_stock.total_trade,
  eod_stock.high,
  eod_stock.low,
  eod_stock.ltp,
  max(eod_stock.datetime),
  company.name 
from
  eod_stock 
  inner join company on (company.code = eod_stock.company_code);

but first one gives me error and 2nd one returns only one row, but i need all 200 companies with their associated info.

Upvotes: 2

Views: 971

Answers (3)

Samia Ruponti
Samia Ruponti

Reputation: 4038

select
  eod_stock.company_code,
  max(eod_stock.close),
  eod_stock.total_volume,
  eod_stock.total_trade,
  eod_stock.high,
  eod_stock.low,
  eod_stock.ltp,
  max(eod_stock.datetime),
  company.name 
from
  eod_stock 
 inner join company on (company.code = eod_stock.company_code) group by eod_stock.company_code;

dagfr was correct all i needed to add group by in the query.

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

The trick here is to start with a list of the max datetime for each company_code, which you can do with this basic query:

SELECT company_code, MAX(datetime) AS maxdatetime
FROM eod_stock
GROUP BY company_code

Join this to a query that gets company code, company name, and end-of-day values, and you should be all set:

SELECT
  company.code,
  company.name,
  eod_stock.open,
  eod_stock.high
FROM eod_stock
INNER JOIN company ON company.code = eod_stock.company_code
INNER JOIN (
    SELECT company_code, MAX(datetime) AS maxdatetime
    FROM eod_stock
    GROUP BY company_code) maxdt
  ON maxdt.company_code = eod_stock.company_code AND
     maxdt.maxdatetime = eod_stock.datetime

Upvotes: 2

CodeIsLife
CodeIsLife

Reputation: 1245

Your first error i guess that your have to write :

Table2 right join Table1

instead of company right join company

the 2nd one to get all company your full join !!

Upvotes: 0

Related Questions