Problem with select query

Databases
1

Hi there, I need ur appreciated help.

My query #1 in MySQL:


SELECT  
  t.CODEC 
, m.max_STATES 
, m.max_date 
, t.DATE_REGISTER 

, t.C2H2_C2H4_PPM
, t.CH4_H2_PPM
, t.C2H4_C2H6_PPM

, PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)) DIFF_MONTH

, m.max_H2_PPM , t.H2_PPM 
, FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_H2 

, m.max_CH4_PPM , t.CH4_PPM 
, FORMAT((max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_CH4 

, m.max_C2H6_PPM , t.C2H6_PPM 
, FORMAT((max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_C2H6 

, m.max_C2H4_PPM , t.C2H4_PPM 
, FORMAT((max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_C2H4 

, m.max_C2H2_PPM , t.C2H2_PPM 
, FORMAT((max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_C2H2 

, m.max_CO_PPM , t.CO_PPM , FORMAT((max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_CO 

, m.max_CO2_PPM , t.CO2_PPM , FORMAT((max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_CO2

FROM 
( SELECT 
CODEC 
, MAX(STATES) max_STATES 
, MAX(DATE_REGISTER) max_date 
, MAX(H2_PPM) max_H2_PPM 
, MAX(CH4_PPM) max_CH4_PPM 
, MAX(C2H6_PPM) max_C2H6_PPM 
, MAX(C2H4_PPM) max_C2H4_PPM 
, MAX(C2H2_PPM) max_C2H2_PPM 
, MAX(CO_PPM) max_CO_PPM 
, MAX(CO2_PPM) max_CO2_PPM 
FROM 
listfiles 
GROUP BY CODEC, DATE_REGISTER) m 
INNER JOIN listfiles t ON m.CODEC = t.CODEC 
AND (m.max_date > t.DATE_REGISTER) 
AND t.CODEC = '7606' 
GROUP BY t.CODEC, DATE_REGISTER
ORDER BY t.ID DESC;

And this is the partial output:


CODEC	max_STATES	max_date	DATE_REGISTER
7606	ALERT		2011-11-16	2009-07-13
7606	ALERT		2009-07-13	2004-06-09
7606	OK		2004-06-09	1998-12-18

But in the table listfiles I have four rows with CODEC where 7606:


ID	CODEC	DATE_REGISTER	STATES	DIFF_MONTH
2258	7606	1998-12-18	OK	66
2460	7606	2004-06-09	OK	61
6254	7606	2009-07-13	ALERT	
8089	7606	2011-11-16	ALERT

If the query #1 correctly working whit ADD alias calculate, not working if interested in the last ID values, because I see the ID number 6254 values and not ID number 8089 values.

The clause ORDER BY t.ID DESC; not working?
I need other query for see the values of ID number 8089?

Can you help me?
Thanks in advance. Cheers

2

no :slight_smile:

in order for me to help you, i would need to understand what the heck you’re trying to accomplish with that query, which you neglected to mention

as it is, all i have to go on is the query itself, and it’s giving me the creeps

(i apologize for using vernacular)

for example, look at this –

SELECT CODEC 
     , MAX(STATES) max_STATES 
     , MAX(DATE_REGISTER) max_date 
     , MAX(H2_PPM) max_H2_PPM 
     , MAX(CH4_PPM) max_CH4_PPM 
     , MAX(C2H6_PPM) max_C2H6_PPM 
     , MAX(C2H4_PPM) max_C2H4_PPM 
     , MAX(C2H2_PPM) max_C2H2_PPM 
     , MAX(CO_PPM) max_CO_PPM 
     , MAX(CO2_PPM) max_CO2_PPM 
  FROM listfiles 
GROUP 
    BY CODEC
     , DATE_REGISTER

this query will return one row for each date_register for each codec

and you want MAX(date_register) for each date_register for each codec

see how easily i can become confused?

3

Thanks for your answer…

I’m sorry, it’s true I’m very confused… but I need check when registering new record in the table mysql.

If exist one same codec in the table I need calculate this algorithm (ADD or increment/increase):

((Value new codec - Value old codec)/number of months between date register new and old)

For example for H2_PPm value:


, m.max_H2_PPM , t.H2_PPM 
, FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.DATE_REGISTER)),3) ADD_H2 

CODEC	max_date	DATE_REGISTER	DIFF_MONTH	max_h2_ppm	h2_ppm
7606	2011-11-16	2009-07-13	28		5500		10,1
7606	2009-07-13	2004-06-09	61		10,1		95
7606	2004-06-09	1998-12-18	66		95		1

The output is:


CODEC	ADD_H2
7606	196
7606	-1392
7606	1424

This algorithm ADD working but I have problem if interested in the see last ID values, because I see the values of record with ID number 6254 and not values with ID number 8089.

4

that sounds like you want to use INSERT… ON DUPLICATE KEY UPDATE

5

Thanks Sir for your suggestion.

I read this article: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

And I have understand that the clause INSERT… ON DUPLICATE KEY UPDATE use the following INSERT.

Unfortunately it is not my case because it’s not always run after insert start control for the codec; this start control depends on the user response to a specific question: if response it’s NO start control and calculate the algorithm (ADD or increment/increase), if response it’s YES I have the stop procedure.

  1. INSERT INTO table;
  2. Specific question for the user;
  3. NO = start control and calculate the algorithm wiht the query SELECT
  4. SI = stop the procedure

If the response it’s NO I need see the values of the LAST INSERT ID where specific codec.
Now I see the values of the last ID but one.

I hope it is enough information.
Thank you Sir once again

6

that makes no sense to me, sorry

you talk about LAST_INSERT_ID but you don’t show your INSERT statement, only that humoungously complicated SELECT