SQLite Forum

”Group by“ Question when use System.Data.SQLite.dll or Sqlite3.dll
Login

”Group by“ Question when use System.Data.SQLite.dll or Sqlite3.dll

(1) By anonymous on 2021-01-18 13:31:58 [link] [source]

Recently,I find a strange question when use the System.Data.SQLite.dll. For example,The database have a table like this:

Name Age School number

w 11 Tai 1

wx 11 Tai 2

wx 11 Tai 3

when I use the command in the C#( System.Data.SQLite.dll): select Name,Age,School,number from db grouy by Name;

The result:

Name Age School number

w 11 Tai 1

wx 11 Tai 3

But when I use the tools sqlitestudio.exe use same command,I get this:

Name Age School number

w 11 Tai 1

wx 11 Tai "2"

I get two different result what make me puzzle. Question1: So if I want to get the second result,what can I do? Question2: why I get two different result?

(2) By Ryan Smith (cuz) on 2021-01-18 13:51:11 in reply to 1 [source]

Because SQLite is free to return ANY of the values from the grouped rows since you did not state which one you want, and I suppose some small change between the engine version that sqlitestudio uses and that system.data.sqlite uses cause different query plans or use different optimizations so that different values are selected.

A query like this:

SELECT A, B, C FROM t GROUP BY A;

Can return a set of values for whihc A is unique and B and C can be any of the B's and C's found in any of the rows that have the same A value.

To get a specific value, you have to use a specific aggregate function (which is something some other database engines FORCE you to do because of this very problem).

For example, if you want the highest B value and Lowest C value from every group of rows with the same A value, then this query will work:

SELECT A, MAX(B), MIN(C) FROM t GROUP BY A;

And it will work in all query engine versions, on any SQLite instance/wrapper/program and even in all DB engines (Like MySQL/MSSQL/PostGres/etc).

The fact that SQLite even allows you to query a group without specifying an aggregate function for every field (B,C) that is not part of the group (A), is a special permission and some of us view it as a deficit or something we wish would be disallowed by maybe a "Strict" mode.

The end take-away is: You need a specific value from inside the grouped values, you need to ask for it, else any random one can pop out, and that may differ between engine versions.

(3) By wxh (wxhwdh) on 2021-01-19 01:24:45 in reply to 2 [link] [source]

I really appreciate your suggestion.

Your idea hit me.

Thank your very much.