creating a "best of" table by merging rows using cell values of rows with highest priority

Question

My table is:

val1   val2  val3   ...    precision   test
-----|-----|-----|-------|-----------|---------
a    | 11  | a   |       | 1         | x
b    | 10  | null|  ...  | 2         | x
a    | 0   | null|       | 3         | x
e    | 13  | b   |       | 1         | y
                    ...

It's actually multiple similar tables that contain values for multiple tests and different processes and techniques in each row. Now, I want to create a "best of" table.

"Best of" table should look like:

val1   val2  val3   ...    precision   test
-----|-----|-----|-------|-----------|---------
a    | 10  | a   |       | 3         | x
e    | 13  | b   |       | 1         | y

The rules are:

group by test
pick for each column a defined (not 0, null, unset) value with highest precision available
if all values should be null or not set just stick to the one with highest precision

Is this even possible in SQLite syntax? Can this be done without a lot of hardcoded subqueries for each column?

That's the point: I'm clueless. Usually I would try to group but the aggregate functions don't seem to help me with this. — Philipp, Jul 13 '16 at 06:31
Your question has a lot of loose ends, assuming that `val1`, `val2`, and/or `val3` could be `NULL` for a given record. Have you thought about such edge cases? — Tim Biegeleisen, Jul 13 '16 at 06:33
thanks for the feedback. I made some changes and tried to clarify the problem. — Philipp, Jul 13 '16 at 06:42
I dont understand your result. The first row with val2=10 is not logical to me. Best of val2 for test x is 11. Please explain the meaning behind this. — Thomas G, Jul 13 '16 at 07:27

score 1 · Accepted Answer · answered Jul 13 '16 at 07:45

Since SQLite 3.7.11, you can use MAX() to determine the row from which the values in other columns are returned:

SELECT val1,
       MAX(precision) AS precision,
       test
FROM MyTable
GROUP BY test;

However, this is not so easy for columns with 0/NULL values, because getting values from different rows would require some aggregation function, and there is none for this. This indeed requires a correlated subquery for each such column:

SELECT val1,
       (SELECT val2
        FROM MyTable AS T2
        WHERE T2.test = MyTable.test
          AND IFNULL(val2, 0) != 0
        ORDER BY precision DESC
        LIMIT 1
       ) AS val2,
       [...],
       MAX(precision) AS precision,
       test
FROM MyTable
GROUP BY test;

i tried to use group_concat() in creative ways as an alternative but in the end it seems that the language is not powerful enough. thanks for your help — Philipp, Jul 14 '16 at 03:30

score -1 · Answer 2 · answered Jul 13 '16 at 07:33

-1

I tried with sql query if number of columns of table is fix. In case you have variable number of columns in table then we have to create dynamic query. For now I have done with static columns.

--Inserting given data into cte table named "tb"
;WITH tb(val1, val2, val3, [precision], test) 
AS
(
    SELECT 'a', 11, 'a', 1, 'x'

    UNION 

    SELECT 'b', 10, NULL, 2, 'x'

    UNION 

    SELECT 'a', 0, NULL, 3, 'x'

    UNION

    SELECT 'e', 13, 'b' , 1, 'y' 

)
--Getting max precision based on test column and conditions
,MaxPrecisionOfTest(test, maxPrecision)
AS
(
    SELECT test,  MAX([precision])
    FROM tb
    WHERE val1 IS NOT NULL
        AND val2 <> 0
        AND val3 IS NOT NULL
    GROUP BY test
)

SELECT tb.*
FROM tb
INNER JOIN MaxPrecisionOfTest ON tb.test = MaxPrecisionOfTest.test
                            AND tb.precision = MaxPrecisionOfTest.maxPrecision

answered Jul 13 '16 at 07:33

Gurpreet Singh

109
4

Why are you putting the semicolon in front of the query? (Hint: SQLite is not SQL Server.) – CL. Jul 13 '16 at 07:37
I am not aware to SQLite. For your information I have used CTE (common table expression) in sql server. Just can try by creating two temporary tables instead of "tb(val1, val2, val3, [precision], test) ", "MaxPrecisionOfTest(test, maxPrecision)" and rest of procedure is same. – Gurpreet Singh Jul 13 '16 at 08:57
I did not ask about the CTE (which is [supported by SQLite](http://www.sqlite.org/lang_with.html)) but about the semicolon. – CL. Jul 13 '16 at 09:26
Please take a look into:- http://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – Gurpreet Singh Jul 13 '16 at 12:26

creating a "best of" table by merging rows using cell values of rows with highest priority

My table is:

"Best of" table should look like:

2 Answers2