CODEWITHSUNDEEP
androidsqlite
Mind Android
Mind Android

Reputation: 568

Android - get max value from sqlite table with condition

I have a sqlite table like...

    id     |    pname    |    ptype    |    amount    
------------------------------------------------------
    1      |     aaaaa   |    qw       |    20    
    2      |     bbbbb   |    qw       |    25    
    3      |     ccccc   |    qw       |    55    
    4      |     aaaaa   |    qw       |    42

i want to get output like

    id     |    pname    |    ptype    |    amount    
------------------------------------------------------
    1      |     aaaaa   |    qw       |    42    
    2      |     bbbbb   |    qw       |    25    
    3      |     ccccc   |    qw       |    55

means non repeative pname with maximum amount... Which query i should run?

Upvotes: 1

Views: 2034

Answers (3)

Shankar Regmi
Shankar Regmi

Reputation: 874

SELECT * FROM table_name ORDER BY amount DESC

Upvotes: 0

CL.
CL.

Reputation: 180010

In SQLite 3.7.11 or later (Android API level 16 or later), you can simply use MAX in the SELECT list, and the other values will be guaranteed to come from the record with the maximum value:

SELECT id,
       pname,
       ptype,
       MAX(amount) AS amount
FROM MyTable
GROUP BY pname

If you need to work with earlier versions, you need to check explicitly:

SELECT *
FROM MyTable
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable AS smaller
                  WHERE smaller.pname  = MyTable.pname
                    AND smaller.amount < MyTable.amount)

Upvotes: 3

flx
flx

Reputation: 14226

Modify your query to

  • group by pname
  • put max(amount) as max_amount into your projection.

E.g.

Cursor c = db.query(table, new String[] {"id", "pname", "ptype", "max(amount) as max_amount"},
        null, null, "pname", null, null);

Upvotes: 1

Related Questions