get maximum value in a field using SQL query

[ponnaboy]

Hi,
I want to retrieve the row containing the maximum value for a particular field.
select max(fieldname) from tablename
How do I do this using database connectivity toolkit.

Shiva

[Feroz]

Hi Shiva,

You can use the DB Tools Execute Query.vi and the DB Tools Fetch Recordset Data.vi to get the result you want.

You'll need to enter the SQL query (just like the one you showed above) into DB Tools Execute Query.vi and get the data using the DB Tools Fetch Recordset Data.vi. The returned data type will be a variant hence you will need to convert it to using Database Variant to Data.vi.

Good luck,
Feroz

[roboticstudent]

Hello,

I am trying to sort data and I am having the same question as Shiva.

My vi shows no error but it brings back no data.

Here's the vi.

Suggestions.

[roboticstudent]

Hello,

I am able to get data,but now the issue is that each of my individual indicators do not reflect the field name when I am querying with sql statements.

This the error I am getting:

"Rec Get Value From Field.vi->Rec Fetch Binary Data (R).vi->EX3.vi<ERR>Exception occured in ADODB.Fields, Item cannot be found in the collection corresponding to the requested name or ordinal.. Help Path is C:\WINDOWS\HELP\ADO270.CHM and context 1240649 in Rec Get Value From Field.vi->Rec Fetch Binary Data (R).vi->EX3.vi"

Any Suggestions??

VI attached.

[Justin_M]

So to clarify, you ARE getting some data back from your query, but not the data you expect to see?  I've tried searching for that error, but I haven't come up with anything.  The error message makes it sound like you are searching for something that doesn't exist in the database.  Have you taken a look at the help file that it points to in the error message?  That may give us more information about what the error message means.

-Justin

[roboticstudent]

Hello,

I figured out the answer. It was sql syntax.

Thanks for coming in here to help.

I created another post earlier because I had a little different question that pertained to fetch.vi.  How do you get an array out from fetch.vi without creating error. I also made type an array also but it does not allow an array.

Thanks for your help.

By the way, how do I as the original poster get rid of a whole thread I created, especially if its a duplicate.

[alecjcook]

Hi. I am having the same problem. Using the "Select Data" vi from within the Connectivity Toolkit, addressing a microsoft access database. I am searching on the column labeled "ID".

The statment "WHERE ID = 4" will work perfectly fine, but the statment "WHERE MAX(ID)" returns nothing. Can you tell me how you fixed the problem please, I am stumped!

Many thanks, Alec

[NToombes]

Having the same issue here. 

Where ID='3' for example works fine but

Where ID=MAX(ID) does not work. 

Did you get any further?

Neil

[RolfO]

---

@NToombes wrote:

Having the same issue here. 

Where ID='3' for example works fine but

Where ID=MAX(ID) does not work. 

Did you get any further?

Neil

---

That is a SQL-problem, not LabVIEW.

max(id) is an aggregate function on id and can not be used in a where clause. If you need to filter on aggregate functions you need to use group by and having. 
https://docs.data.world/documentation/sql/concepts/intermediate/HAVING.html

But you will not be able to do what you want since group by / having  will not accept the clause "id = max(id)".

I do not know what you really want to accomplish, it may be possible to use offset/fetch:
select * from <table> order by id desc fetch first 1 row only;
I think this is standard SQL. It works in PostgreSQL, but may not work on your database / database server.

The query selects all elements in table, sort them descending on the id-column and returns the first row of the result.

[NToombes]

Hi RolfO

Thanks for your reply / message. I actually came up with another workaround which might not be as nice but it does work.

So, used the max(id) to just retrieve the maximum value (last entry as its an auto incrementing value) and then used the returned value in the where statement to retrieve the whole row of data associated to that max ID value. I have attached an example image of the code incase it helps others but don't want others thinking I am teaching them to suck eggs 🙂

Neil