CODEWITHSUNDEEP
javaandroidsqldatabasesqlite
rennoDeniro
rennoDeniro

Reputation: 928

Android SQLite query order issue

How can I query a database to display a table with data by two different IDs, yet alternate each ID per row and yet still order by time. For example. Database has 10 Elements of ID1 and 10 Elements of ID2.

I need it to be:

ID1 #data #2minutes ago

ID2 #data #15minutes ago

ID1 #data #4minutes ago

ID2 #data #50minutes ago

Any Ideas?

UPDATE: The number of ID's must be able to differ from one to n and also I am currently trying to do this via the SQLite.query() method.

UPDATE 2: The #data represent columns of data within that row, not just one, so there could be 5 columns for example. Also, The data has to be returned via a cursor.

UPDATE 3: Further elaboration, below is a table and expected result I am trying to achieve.

The input table (Ordered by Time)..

id | name | number | time
01   Tim    561481   2 minutes ago
02   Jon    951484   5 minutes ago
02   Jon    978284   7 minutes ago
04   Zen    171484   15 minutes ago
04   Zen    171484   17 minutes ago
03   Ken    468488   20 minutes ago
02   Jon    978284   32 minutes ago

And the output: note, I need to alternate the Id's within the data base where possible and yet still order the whole thing by time, for example:

id | name | number | time
01   Tim    561481   2 minutes ago
02   Jon    951484   5 minutes ago
04   Zen    171484   15 minutes ago
03   Ken    468488   20 minutes ago
02   Jon    978284   7 minutes ago
04   Zen    171484   17 minutes ago
02   Jon    978284   32 minutes ago

UPDATE 4: Prefer a very simple solution such as a complex yet clean sql query etc. UPDATE 5: Despite having to give the bounty away, this question still has not been answered with an simple solution to do what I desire.

Upvotes: 8

Views: 1277

Answers (6)

mikebabcock
mikebabcock

Reputation: 791

Your data seems to be organized very poorly. If you have multiple tables of seemingly identical data that differ only in type, the data should be stored in one table with a type column instead.

Consider table checkin (id, user, datetime) and table checkout (id, user, datetime) which could be merged into table checkinout (id, user, datetime, inorout).

When the data is structured optimally, you'll find the queries to work on that data are much easier to design.

Also cf. any tutorial on normalization or http://en.wikipedia.org/wiki/Database_normalization

Upvotes: 0

Tony Shih
Tony Shih

Reputation: 436

This query will work if the same id may not be possible to have same time.

select id, name, rc from
(select id,  name , @rownum:=@rownum+1 as rc from tbl,
(select @rownum:=0) r 
order by (cast(substring(time,1,2) as UNSIGNED)/rc)*cast(ascii(substring(name, 1,1)) as unsigned), time asc) data

rownumber calc by courtecy

All I am trying to do is a give a weight factor to each row in the table.

Upvotes: 0

pawelzieba
pawelzieba

Reputation: 16082

SELECT t.id, t.time, 
    (
        SELECT count(*) 
        FROM tab u 
        WHERE u.id = t.id AND u.time < t.time 
        ORDER BY u.time
    ) counter 
FROM tab t 
ORDER BY counter, t.time

I hope I have understood your question correctly.

Output for random data:

id  time  counter
2   2   0
0   7   0
4   16  0
1   21  0
3   50  0
2   5   1
1   29  1
0   32  1
4   38  1
3   69  1
2   25  2
1   30  2
0   37  2
4   53  2
2   51  3
4   59  3
0   87  3
4   62  4
2   73  4
0   90  4

Don't forget to use indexes.

Upvotes: 2

Trinimon
Trinimon

Reputation: 13967

Try the following:
1. create SELECT statements for both sets (items with ID1 and items with ID2)
2. use ORDER BY within those statements for time/IDx order
3. add a column 

(SELECT COUNT(*) FROM table AS tab2 WHERE tab2.item <= tab1.item) AS rownum

4. add list identifiers for both sets
5. use a UNION to unite both result sets
6. select from that united list and compose a field that is built as (rownum||set identifier)
7. add an ORDER BY the composed field to the whole expression

Update: this was rather intended for two tables; after looking at the example updates above and trying some things I think it's rather hard to code that in a single SQL (using SQLite)

Upvotes: 1

RMalke
RMalke

Reputation: 4094

The only solution I found it you to perform two queries

First query for the data sorted by ID and Date

SELECT ID,
       Date,
       COUNT(1) AS TotalRows
FROM Table
ORDER BY ID, Date

And then query the different IDs you have, store it in an array, let's call it idsArray

SELECT COUNT(1) FROM Table GROUP BY ID

Read all the result from the first query into a Queue: pseudo-code:

Queue<YourData> yourQueue = // read it from database cursor

ArrayList sortedData = new ArrayList();

YourData d = null;

String lastId = null;

int position = 0;

while((d = yourQueue.dequeue()) != null) {

    if (lastId == null || d.id.equals(idsArray[position])) {

        sortedData.add(d);

        lastId = d.id;

        if (position < idsArray.length -1) {
            position ++;
        } else {
            position = 0;
        }
    } else {
        yourQueue.enqueue(d);
    }

}

Upvotes: 0

bwperrin
bwperrin

Reputation: 692

something like:

select a, b, c from (
    select a, b, c, rank = row_number() over (order by time) * 2 FROM ID1
    UNION ALL
    select a, b, c, rank = row_number() over (order by time) * 2 + 1 FROM ID2 ) t
order by rank

[edit: my bad - not available in SQLite]

Upvotes: 0

Related Questions