StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POgroup and sort sqlite records
text
Body
copied!<p>I have a table with the following cols</p> <pre><code>_id INTEGER PRIMARY KEY, body TEXT, category TEXT, is_readed INTEGER, date INTEGER </code></pre> <p>i have the records <strong>look the example</strong></p> <p>i have the following query</p> <pre><code>SELECT _id, body, category, is_readed FROM table GROUP BY category ORDER BY category, is_readed, date DESC </code></pre> <p>i want show <strong>only the first record</strong> for category (regardless of whether is_readed is 0 or 1) but i want show first (if exist) the record with <code>is_readed == 1</code>. but sometimes show first record with <code>is_readed == 0</code> even if exist one with <code>is_readed == 1</code></p> <p><strong>Note</strong>: I'm using ContentProvider not raw queries</p> <h3>Update</h3> <p>after try a while with this roughly work</p> <pre><code>SELECT _id, body, category, MIN(is_readed) as is_readed FROM table GROUP BY category ORDER BY category, date DESC </code></pre> <p>i still are making tests but I'm still not convinced</p> <h3>Examples</h3> <pre><code>SELECT * FROM table ORDER BY category, is_readed ASC, date DESC; _id|category|body|is_readed|date 19|Hogar|message1|1|1371449889136 16|Hogar|message2|1|1371449806704 15|Hogar|message2|1|1371449803825 11|Hogar|message3|1|1371448915930 5|Hogar|message4|1|1371447395055 4|Hogar|message4|1|1371447391394 23|Linea blanca|message2|0|1371450430216 26|Linea blanca|message1|1|1371450719124 24|Linea blanca|message4|1|1371450431604 21|Linea blanca|message1|1|1371449893835 20|Linea blanca|message1|1|1371449891488 17|Linea blanca|message3|1|1371449810104 13|Linea blanca|message3|1|1371448994173 12|Linea blanca|message2|1|1371448917864 6|Linea blanca|message4|1|1371447397387 22|Vehiculos|message3|0|1371450428817 14|Vehiculos|message3|0|1371449801144 25|Vehiculos|message4|1|1371450717115 18|Vehiculos|message4|1|1371449887682 10|Vehiculos|message1|1|1371448422563 9|Vehiculos|message4|1|1371448419438 8|Vehiculos|message3|1|1371448416315 7|Vehiculos|message4|1|1371448395644 3|Vehiculos|message3|1|1371447388887 2|Vehiculos|message1|1|1371447386126 1|Vehiculos|message2|1|1371447383557 </code></pre> <hr> <h3>My Update</h3> <pre><code>SELECT _id, body, category, MIN(is_readed) as is_readed FROM table GROUP BY category ORDER BY category, date DESC _id|category|body|is_readed|date 4|Hogar|message4|1|1371447391394 23|Linea blanca|message2|0|1371450430216 14|Vehiculos|message3|0|1371449801144 </code></pre> <hr> <h3>@Hoan Nguyen answer</h3> <pre><code>4|Hogar|message4|1|1371447391394 6|Linea blanca|message4|1|1371447397387 1|Vehiculos|message2|1|1371447383557 </code></pre> <hr> <h3>Expected result</h3> <pre><code>19|Hogar|message1|1|1371449889136 23|Linea blanca|message2|0|1371450430216 22|Vehiculos|message3|0|1371450428817 </code></pre>

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