how to define in the optional the clause, the maximum value of a colum

[larson09]

Hi!

I'm using select data block from database toolset.

I want to use optional clause wiring to import only the maximum value of the column 2.

E.g. to import only the values greater than 1 you have to wire a string like this col2>1. But i don't know which string i have to wire to iport the maximum value of this column.

Thank you in advance.

Larson

[Dennis_Knutson]

The SQL query for something like this is:

SELECT MAX(column2) FROM tablename

I don't know the name of the function in the toolkit to directly execute a SQL query since I've never really found a need for the toolkit itself. I know there is such a function though. It's probably a lower level VI.

[larson09]

But where you wire this command?

I want to do in the way you can see in the vi i've attached. I want to use select all data to import the column 2&3 where col1 is maximum.

My question is what string i have to wire to do it in this way. where max(col1)...max(col1)??¿¿

Thanks!

LArson 

[becktho]

Hi

In your picture it all is wired correctly, you just have to pass the correct "Where"-clause. I'm not sure, but I think you can't use "Max" in a where clause.

So I'd suggest you to make two selection. The firs (Select Max(col1) From table) to get the maximum value and a second to get the data. There you can use the vi which is in your picture and wire a where-clause (WHERE col1=[result from 1st select]).

Hope this helps.

Thomas

[larson09]

Hi!

I don't undertand your first step.....where i have to wire Select max(col1) from table ??? to select all data block?? or to another block? I don't undertand it.

Thanks!

[becktho]

Hope this makes it clear.

Thomas

[larson09]

Hi!

I have a problem.......the column i have to find the maximum value is a colum of time 10:21:13 and i don't know why the maximum value the vi import from the database is today's data 14/12/05 (?*¿*). I don't know why. I'm tried to find the maximum of a number's column and it works perfect.

Another thing about this i want to ask you is to use an event structure to only run this subvi when a new row is written, how can i do this? CAn i know the number of rows written without importing all the data of the table?

Thank you very much Becktho

Larson

[becktho]

I can't tell you why it doesn't work or how you could make it work using time. I always have an Index column in each table of an auto-incrementing type. So to use this is very easy as you already said - numbers work without problem.

Using an event-structure I just can say yes and no. Do you write the data on your own or is it written by another program? How is your program structured?

To read the number of rows you can use this statement: "SELECT Count(data.col1) AS numRows FROM data". This will give you one value - the number of rows in your table.

[larson09]

Hi again becktho!

I think the better way to import what i want is knowing the number of rows written using the command you told in the last post.

But now i need to know which is the command to select the elements in the row 'n' of column 1, 2 and 3. Select col1(n)&col2(n)&col3(n) from data (?¿?¿).

Do you know which is the command to import the row n of some columns of a table.

Thank you very much for helping.

P.D:i'm looking for a manual to learn how to construct this commands but i haven't found, where can i find one?

Larson

[becktho]

Hi Larson

To select row n you either need a certain value (e.g. an index or something you know is unique in row n) or you select all values and extract one row in LV.
So the first statement would be "SELECT * (* means all columns, you could also specify the columns as well) FROM data WHERE [a value = another value]. This would give you just the rows where the where-clause is correct. The other way is to select all data and just extract row n from the array you get from the recordset.

There might be other ways, but these are two solutions I just had in mind.

Manuals - just google the internet for SQL (Structured Query Language) and you will find lots of websites providing this information.

Hope this helps.

Thomas