Databases
StackOverflow2013
Postsdbo
POGet data Sorted by descending size according units of measurement in SQLite
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet data Sorted by descending size according units of measurement in SQLite
    text
    copied!<p>I have a table with these column</p> <pre><code>|Name|Quantity|Unit </code></pre> <p>And I want to get the data sorted by descending size according units of measurement for example:</p> <pre><code>|Bread|1.2|Kg |Pasta|600.21|g |Flour|200.18|g |Salt|70.12|mg |Pepper|60.3|mg |Venom|700.15|mcg </code></pre> <p><em>1.2</em> is minor than <em>600.21</em> but the unit is <em>Kg</em> so in the order priority is before according scale <em>Kg > g > mg >mcg</em> etc. (assuming that the table doesn't contains 6000g because the notation is 6Kg)</p> <p>This code clearly doesn't works</p> <pre><code> public Cursor allDataSorted() { try { Cursor c = mDb.rawQuery( "SELECT * FROM myTable order by Quantity desc", null); if (c != null) { c.moveToNext(); } return c; } catch (SQLException mSQLException) { Log.e(TAG, "allDataSorted&gt;&gt;" + mSQLException.toString()); throw mSQLException; } } } </code></pre> <p>And all data in my table are text String so <em>956</em> for example is wrongly showed after <em>99</em>, so I have no idea of How I could solve this problem and the result is totally unsorted.</p> <p>Any suggestion?</p> <p>NB</p> <p>I cannot convert and reconvert the tables, these are too big, and the adapter is used for too many kind of units to use only a general base unit. </p> <p><strong>EDIT</strong></p> <p>Following the suggestion of Ryan Griggs I have included <em>mapperTb</em> table in the same db file</p> <pre><code>Unit|Multipler hg|100 g|1 dg|0.1 cg|0.01 mg|0.001 mcg|0.000001 </code></pre> <p>And I have edit the allDataSorted() method in this way:</p> <pre><code> public Cursor allDataSorted() { try { Cursor c = mDb.rawQuery("SELECT *, (CAST(myTable.Quantity as FLOAT) * mapperTb.Multipler) as Quantity FROM myTable INNER JOIN mapperTb ON myTable.Unit = mapperTb.Unit ORDER BY Quantity desc", null); if (c != null) { c.moveToNext(); } return c; } catch (SQLException mSQLException) { Log.e(TAG, "allDataSorted&gt;&gt;" + mSQLException.toString()); throw mSQLException; } } } </code></pre> <p>but doesn't works and the Cursor results empty</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload