CODEWITHSUNDEEP
sqlite
user2901528
user2901528

Reputation: 11

SQLite Query Multiple Tables using MAX column in one table

I'm new to SQLite and I'm having a problem getting a query with multiple tables to work correctly.

Table1

MessageNum    Regression     RegressionNum
===========   ==========     =============
1234567890    true           12340000
1234567890    true           23450000
1234567890    true           34560000

Table2

MessageNumber    Data     ChangeNumber      
=============    ====     =========
1234567890       text2    1234567
1234567890       text8    2345678
1234567890       text9    3456789
1234567890       text4    4567890
1234567890       text5    5678901
1234567890       text3    6789012
1234567890       text1    7890123

I'd like the query to return only one row of data in table 2 based on MAX(ChangeNumber)... and all matching rows from table 1.

So the results should be

MessageNum    Regression     RegressionNum    Data     ChangeNumber 
===========   ==========     =============    ====     ============
1234567890    true           12340000         text1    7890123  
1234567890    true           23450000         text1    7890123
1234567890    true           34560000         text1    7890123

I can't seem to get the query results in the format listed above. I've used JOIN and SUB SElECTS but nothing seems to work correctly.

The query should contain the following the conditions below. 

table1.MessageNum=1234567890 AND table1.Regression="true" AND MAX(table2.ChangeNumber)

Upvotes: 1

Views: 214

Answers (2)

Edper
Edper

Reputation: 9322

Using MAX aggregate function, try this one:

SELECT T2.MessageNumber, T1.Regression, T1.RegressionNum, T2.Data, T2.ChangeNumberMax 
FROM (SELECT MessageNumber, MAX(ChangeNumber) as ChangeNumberMax, Data
FROM Table2
GROUP BY MessageNumber) T2
INNER JOIN Table1 T1
ON T2.MessageNumber=T1.MessageNum
WHERE T1.MessageNum=1234567890 AND T1.Regression="true"

Upvotes: 0

lc.
lc.

Reputation: 116528

You can use a WHERE NOT EXISTS on Table2 to get only the maximum row:

SELECT t1.MessageNum, t1.Regression, t1.RegressionNum, t2.Data, t2.ChangeNumber
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.MessageNum = t2.MessageNumber
WHERE NOT EXISTS (
    SELECT 1 
    FROM Table2 t2a 
    WHERE t2a.MessageNumber = t2.MessageNumber 
    AND t2a.ChangeNumber > t2.ChangeNumber
)
AND t1.MessageNum = 1234567890
AND t1.Regression = "true"

SQL Fiddle demo

Upvotes: 1

Related Questions