get maximum value in a field using SQL query

[jcarmody]

I'm a little late to the party, but want to add this for posterity.  You can use the DB Tools Select vi to get an aggregate from a table by putting the aggregation in the table name.

[FireFist-Redhawk]

---

@jcarmody wrote:

I'm a little late to the party, but want to add this for posterity.  You can use the DB Tools Select vi to get an aggregate from a table by putting the aggregation in the table name.

---

That is not a very good practice if you ask me. If you go far enough under the hood, you will find that you can even put conditions (WHERE ..., ORDER BY ...) after the table name too and it will work. But just because you can doesn't mean you should. 

And for the record, the easiest way to accomplish this IMO? Add "ORDER BY [column name] DESC" as the condition and take the first result. Boom, done.

[drjdpowell]

One could also execute SQL like this:

SELECT * FROM MyTable WHERE MyField=(SELECT Max(MyField) From MyTable);

[Yamaeda]

---

@drjdpowell wrote:

One could also execute SQL like this:

SELECT * FROM MyTable WHERE MyField=(SELECT Max(MyField) From MyTable);

---

Wouldn't 'SELECT Max(MyField) From MyTable' yield the same result?

[RolfO]

---

@jcarmody wrote:

I'm a little late to the party, but want to add this for posterity.  You can use the DB Tools Select vi to get an aggregate from a table by putting the aggregation in the table name.

---

This may give you max(id), but not solve the problem. The problem is getting the whole row identified by max(id).

[RolfO]

---

@Yamaeda wrote:

---

@drjdpowell wrote:

One could also execute SQL like this:

SELECT * FROM MyTable WHERE MyField=(SELECT Max(MyField) From MyTable);

---

Wouldn't 'SELECT Max(MyField) From MyTable' yield the same result?

---

No.

Your solution only returns max(MyField).

The question was for the complete row for max(MyField), which is what the query from @drjdpowell will return.

[drjdpowell]

---

@Yamaeda wrote:

---

@drjdpowell wrote:

One could also execute SQL like this:

SELECT * FROM MyTable WHERE MyField=(SELECT Max(MyField) From MyTable);

---

Wouldn't 'SELECT Max(MyField) From MyTable' yield the same result?

---

I think you mean 'SELECT *, Max(MyField) From MyTable', which gets all the fields of the maximum row.  

It depends if there is more than one row with the maximum value, in which case your version will only get one row, but the first version will get all such rows.  

[RolfO]

---

@drjdpowell wrote:

---

@Yamaeda wrote:

---

Wouldn't 'SELECT Max(MyField) From MyTable' yield the same result?

---

I think you mean 'SELECT *, Max(MyField) From MyTable', which gets all the fields of the maximum row.  

It depends if there is more than one row with the maximum value, in which case your version will only get one row, but the first version will get all such rows.  

---

'SELECT *, Max(MyField) From MyTable' is quite non-standard SQL. Which database is this?

[drjdpowell]

That's SQLite: https://www.sqlite.org/lang_select.html

It may not apply to other DBs.