Sqlite Equivalent of Postgresql's Greatest Function

SQLite equivalent of PostgreSQL's GREATEST function

SELECT MAX(1,2,..)

ref: https://sqlite.org/lang_corefunc.html#maxoreunc

max(X,Y,...)

The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL. The multi-argument max() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to max() define a collating function, then the BINARY collating function is used. Note that max() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.

SQLIte MAX vs Postgresql GREATEST, with strings

Firstly, the length of a string is irrelevant. It's looking at the highest value according to collating sequence.

• https://www.sqlite.org/lang_corefunc.html#maxoreunc

Secondly, the default collating sequence is BINARY and so it's looking at the ASCII values.

• https://www.sqlite.org/datatype3.html#collation_sequence_examples
• https://en.wikipedia.org/wiki/ASCII
• In the ASCII table, r has a value of 162, and V has a value of 126, so r is higher...
• You can force NOCASE : http://sqlfiddle.com/#!5/f5d34/2

Thirdly, it is not relevant that you're using MAX() as an aggregating or scalar function, however you use it gives the same result.

• http://sqlfiddle.com/#!5/6fbe6/1

Finally, the implication that PostgreSQL gives the longest string when using GREATEST() is wrong too...

• It just has a different collation sequence (which order the characters are valued) : http://sqlfiddle.com/#!15/6fbe6/4

Is there a SQLite equivalent to COPY from PostgreSQL?

You may read the input file into a string and then insert it:

sql = "INSERT INTO Pub (k, p) VALUES ('pubFile.txt', ?)"
with open ("pubFile.txt", "r") as myfile:
    data = '\n'.join(myfile.readlines())
cur = conn.cursor()
cur.execute(sql, (data,))
conn.commit()

sqlite or mysql for large datasets

The SQLite database engine stores the entire database into a single file. This may not be very efficient for incredibly large files (SQLite's limit is 2TB, as you've found in the help). In addition, SQLite is limited to one user at a time. If your application is web based or might end up being multi-threaded (like an AsyncTask on Android), mysql is probably the way to go.

Personally, since you've done tests and mysql is faster, I'd just go with mysql. It will be more scalable going into the future and will allow you to do more.

MongoDB equivalent of SQL greatest()

MongoDb doesn't currently have the equivalent to the GREATEST function. You could use a MapReduce, but it won't provide efficient immediate results. Additionally, you wouldn't effectively be able to return the other fields of the document. You'd need to do more than one query, or potentially duplicate all of the data. And, without running an update process for the results, it wouldn't be up to date as documents were modified, as a Map Reduce in MongoDb must be initiated manually.

The MongoDb aggregation framework wasn't intended for this pattern, and if it is possible, would result in a very lengthy pipeline. Also, it's currently limited to 16MB of results and doesn't easily return more than the fields you've aggregated. Returning select * requires a manual field projection, potentially more than once depending on the desired output.

Given that you want to return multiple fields, and the result isn't an aggregation, I'd suggest doing something far simpler:

Precompute the result of a call to the greatest function and store it in the document as a new field for easy access in a variety of queries.

Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

You'd need to make a User-Defined Function if you wanted to have syntax similar to your example, but could you do what you want to do, inline, fairly easily with a CASE statement, as the others have said.

The UDF could be something like this:

create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
  if @val1 > @val2
    return @val1
  return isnull(@val2,@val1)
end

... and you would call it like so ...

SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice) 
FROM Order o

query to sort rowwise in sql

In SQLite, you can do this using the MIN and MAX functions, as detailed here

SELECT MIN(column1, column2), MAX(column1, column2) FROM mytable

You originally didn't specify the DB type, so I wrote this for MySQL. The logic applies to others as well, though the functions may differ.

SELECT LEAST (column1, column2), GREATEST(column1, column2) FROM mytable

• GREATEST function
• LEAST function

select top n record from each group sqlite

You could use a correlated subquery:

select  *
from    ResultView rv1
where   SubjectId || '-' || StudentId || '-' || LevelId in
        (
        select  SubjectId || '-' || StudentId || '-' || LevelId
        from    ResultView rv2
        where   SubjectID = rv1.SubjectID
        order by
                total desc
        limit   2
        )

This query constructs a single-column primary key by concatenating three columns. If you have a real primary key (like ResultViewID) you can substitute that for SubjectId || '-' || StudentId || '-' || LevelId.

Example at SQL Fiddle.

---

Related Topics