Problem with Update query

Databases
21

sorry, i am still a long way from understanding, and to be honest, it’s not going to help you very much

you realize those MAX values that you produce in the “m” subquery don’t all come from the same row, right?

so if you’re trying to compare, for example, “1.number of months between a first and the next record” you aren’t getting consecutive rows at all

22

Ok I understand…

But perhaps explain step by step.

how to compare two records when they have the same code_A ?

23

how do you identify those two rows?

sure, you have a primary key on the table, but it’s only an auto_increment, and it’s useless for this task

so how would you suggest identifying consecutive rows? how are you going to find the id numbers of the rows you want to compare?

24

Good question Sir.

Each row is different and for each row in the table I have:

  1. the code_A
  2. myDate
  3. CO_PPM
  4. CO2_PPM
  5. H2_PPM
  6. CH4_PPM
  7. C2H6_PPM
  8. C2H4_PPM
  9. C2H2_PPM
  10. STATUS

The field myDate for each row with the same code_a is crucial for identifying consecutive rows.

I don’t say that my solution is right or final.

25

now that you have finally revealed that you’re not interested in the collective MAXes of your data columns, but rather, want consecutive rows compared, i’m afraid i just don’t have the time to walk you through a complete rebuilding of your query

if you have specific issues, like a syntax error or something, maybe i can take a look

otherwise, you’re going to have to rebuild the query yourself

one other factor to consider is that comparing consecutive rows is something that is often accomplished far more efficiently in the application, i.e. you just return sorted rows to the application and the application compares one row to the next

26

I think use MAX in my data column for execution comparison of dates in the row with same code_A.
If I need complete rebuilding the query I don’t have idea to start …

27

Thanks Sir.

Now I try this query (without maxDate).

And I have this output:


code_a	mydate		id	diff_month	h2_ppm	h2_ppm1	inc_h2
5550	2009-07-28	6011	69		12	97	-1.231884	
5550	2005-10-27	3408	24		13	97	-3.500000
5550	2003-10-07	2309	0		97	97	null

The problem is h2_ppm1 value is always the h2_ppm1 value of rows id #2309…

Right output:


code_a	mydate		id	diff_month	h2_ppm	h2_ppm1	inc_h2
5550	2009-07-28	6011	69		12	13	-0,0144	
5550	2005-10-27	3408	24		13	97	-3.5000
5550	2003-10-07	2309	0		97	97	null


SELECT a.CODE_A
     , b.myDate
     , b.ID
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM b.myDate)
     , EXTRACT(YEAR_MONTH FROM a.myDate)) DIFF_MONTH
     , b.H2_PPM
     , a.H2_PPM 
     , (b.H2_PPM-a.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM b.myDate) 
     , EXTRACT(YEAR_MONTH FROM a.myDate)) INC_H2 
 
FROM listfiles_2011_11_25 a
JOIN listfiles_2011_11_25 b
ON a.CODE_A = b.CODE_A

WHERE 1 
AND a.CODE_A = '5550'

GROUP BY CODE_A, myDate
ORDER BY ID DESC;
28

the problem is the GROUP BY – remove it

29

Ok Sir:


SELECT a.CODE_A 
     , b.myDate 
     , b.ID 
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM b.myDate) 
     , EXTRACT(YEAR_MONTH FROM a.myDate)) DIFF
     , b.H2_PPM 
     , a.H2_PPM  
     , (b.H2_PPM-a.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM b.myDate)  
     , EXTRACT(YEAR_MONTH FROM a.myDate)) INC_H2  
  
FROM listfiles_2011_11_25 a 
JOIN listfiles_2011_11_25 b 
ON a.CODE_A = b.CODE_A 

WHERE 1  
AND a.CODE_A = '5550' 

ORDER BY ID DESC;

Output (rows with ID x 3):


code_A	myDate		ID	Diff	H2_PPM	H2_PPM1	inc_H2
5550	2009-07-28	6011	69	12	97	-1.231884
5550	2009-07-28	6011	45	12	13	-0.022222
5550	2009-07-28	6011	0	12	12	
5550	2005-10-27	3408	24	13	97	-3.500000
5550	2005-10-27	3408	0	13	13	
5550	2005-10-27	3408	-45	13	12	-0.022222
5550	2003-10-07	2309	0	97	97	
5550	2003-10-07	2309	-24	97	13	-3.500000
5550	2003-10-07	2309	-69	97	12	-1.231884
30

when you join the same table to itself on a.CODE_A = b.CODE_A, this is what you get

are you still going to try to compare consecutive rows with sql?

i think at this point you should be doing it in the application

31

Application: Language server side?
I try with ASP and print the same output with SQL…

32

[sigh]

no, i meant, return the entire table, a new/different query, using ORDER BY to get the rows into the right sequence, and then detect the consecutive rows with ASP logic

33

Sorry I don’t understand.

34 
SELECT [I]columns[/I]
  FROM [I]yourtable[/I]
[COLOR="#0000FF"]ORDER
    BY code_a
     , mydate[/COLOR]

:slight_smile:

35

thanks you my friend, I have solution with language server side.