Query to get the last data

PedroMB
04-16-2004, 08:34 AM
Hello

Imagine you have the following table

Year | Month | Var | Value
-------------------------------------------
2000 10 Var1 test1
2000 10 Var2 test2
2000 10 Var3 test3
2000 10 Var4 test4
2000 11 Var2 test5
2000 11 Var4 test6
2000 12 Var2 test7

Who can I make an SQL select to get all the value of the variables with only the last value.

Exemple:

If I want the 12 month of 2000 I have the values for the following variables:
Var1=test1
Var2=test7
Var3=test3
Var4=test6

Shurik12
04-16-2004, 08:45 AM
Hi,

One of the ways (presuming you are using Access):


"SELECT Top 1 * From Table1 Order By [month] Desc"

PedroMB
04-16-2004, 08:51 AM
Sorry, I using mySQL

I going to try

....

Shurik12
04-16-2004, 08:54 AM
In MYSQL you have to try to use "...Limit 1..." instead

PedroMB
04-16-2004, 09:02 AM
for a table with name 'dbdata', I have a sintaxe error for:

SELECT LIMIT 1 * From dbdata Order By month Desc

?????

PedroMB
04-16-2004, 09:12 AM
I can use limit only to limit the number of row to show, and not to select only the last value....

???

Shurik12
04-16-2004, 09:27 AM
I don't have MySQL by hand but quite sure this should work for you:

"SELECT * From dbdata Order By month Desc LIMIT 1"

Shurik.

PedroMB
04-16-2004, 09:37 AM
I don't have MySQL by hand but quite sure this should work for you:

"SELECT * From dbdata Order By month Desc LIMIT 1"

Shurik.

Sorry, but the query only give me 1 line of data (because of LIMIT 1).

Maybe I explain wrong. I want to get all values of var's. I need to get Var1,Var2,Var3 and Var4, but the value must be always the exist value of the last month.

I need this concept because I have a database to store 100 diferent var's and I don't want to create 100 columns, and I need to store the last month value if is diferent from the previous month...

I like in one query to get this data. If is impossible, I have to program code to do the same with diferent querys...

Shurik12
04-18-2004, 10:18 AM
Sorry I have difficulties to understand what you'd like to achieve.
Just had a look at the intial post of you:


Year | Month | Var | Value
-------------------------------------------
2000 10 Var1 test1
2000 10 Var2 test2
2000 10 Var3 test3
2000 10 Var4 test4
2000 11 Var2 test5
2000 11 Var4 test6
2000 12 Var2 test7

Who can I make an SQL select to get all the value of the variables with only the last value.

Exemple:

If I want the 12 month of 2000 I have the values for the following variables:
Var1=test1
Var2=test7
Var3=test3
Var4=test6



May be you could try to explain it one more time?

Shurik.

PedroMB
04-19-2004, 07:40 AM
Sorry I have difficulties to understand what you'd like to achieve.
Just had a look at the intial post of you:



May be you could try to explain it one more time?

Shurik.

New Explanation:

My objective is to store values of 100+ variables.
This variables can change month by month, so, in one year I have 1200 values.
Because I want to control more the 100 independent systems, during 6 years, I have 1200*6*100=720000 values to store.
I don't know if I only have 100 variables. I think this number will increase, so, one technic I can use is to store the values o change in the month.
I i can do that, the number of var will be only 20 - 30 % of total by month.

The problem is to get the var by system. If I want to know the 100 var of last month I have to make a query to get the last month, but when the var doesn't exist in the last month, I have to get the value in the previous month and so one...

I can I make this work with querys only...

This is the exemple of the inicial table. I get the var of the last month (12), but I only find one value. I have to go in 11 month and get more 2 var. Because it's not finish (I don't have all vars), I must have to go in previous month (10) and get the remain vars. Don't forget the var value always must be the must update (the last one).

Can you help ???
If don't work with querys, I must have to do code...

Thanks

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum