CODEWITHSUNDEEP
sqlitedistinct
SubjectX
SubjectX

Reputation: 896

SQLite query with LAST and DISTINCT

I have an example table:

ID  |  ArticleID  |  Price  |  SupplierID  |  dateAdded
1      1             100          1             2014-08-01
2      1             110          2             2014-08-01
3      2             105          1             2014-08-01
4      2             106          1             2014-08-01
5      2             101          2             2014-08-01
6      3             100          1             2014-08-01
7      1             107          2             2014-09-01
8      3             343          2             2014-09-01
9      3             232          2             2014-09-01
10     1             45           1             2014-09-01

I want to use .query on this table and select LAST value entered for each DISTINCT ArticleID for each SupplierID, resulting in:

ID  |  ArticleID  |  Price  |  SupplierID
10     1             45          1
9      3             232         2
6      3             100         1
7      1             107         2
4      2             106         1
5      2             101         2

I want to get price for last ArticleID entered for each SupplierID.

What should I enter into

public Cursor query (boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

I came up with this so far: 

String[] columns = new String[]{DatabaseOpenHelper.KEY_ID, DatabaseOpenHelper.KEY_CENA, DatabaseOpenHelper.KEY_IZDELEK_ID};
Cursor crs = database.query(true,"prices", columns, selection, selectionArgs, null, null, null, null);

but now I'm stuck:S

Any hint how to do this?

You can also suggest raw query if possible..

Upvotes: 0

Views: 106

Answers (2)

SubjectX
SubjectX

Reputation: 896

After fidling around a bit and help of a friend I have came with SQL query that does what I want, not sure about optimization:

select tab.* from cene tab inner join (
    select izdelek_id, trgovina_id, Max(enter_date) as maxDate
    from cene group by izdelek_id, trgovina_id) art
on (art.izdelek_id = tab.izdelek_id) and (art.trgovina_id = tab.trgovina_id) and (art.maxDate = tab.enter_date)

izdelek_id = ArticleID
trgovina_id = SupplierID
cene is the name of a table.

Hope it helps to somebody..

Upvotes: 0

chalup
chalup

Reputation: 8516

Raw query would be like this:

SELECT ID, ArticleID, Price, SupplierID FROM your_table WHERE ID IN (SELECT max(ID) from your_table GROUP BY ArticleID, SupplierID);

I assumed the IDs are autoincremented and the more recent entries have higher ids. If that's not the case change the HAVING clause to operate on DATE column.

Upvotes: 2

Related Questions