CODEWITHSUNDEEP
androiddatabasesqlitesortingcursor
sjain
sjain

Reputation: 23354

Sorting data from two different sorted cursors data of different tables into One

I want to sort the results coming from two cursors.

Lets consider two cursors to be cursor1 and cursor2.

Cursor1 is used for table X and Cursor2 is used for table Y.

Cursor1 is using column Start of type date to sort the data and Cursor2 is using column Date of type date to sort the data.

Some fields are common in both the tables and some are not.

However, there is no absolute relation between the two tables.

The problem is apparent. The combined data should be the sorted list from both the cursors.

Right now what's happening is:

I am getting sorted list from cursor Cursor1 that is independent of the sorted list coming from cursor Cursor2.

How to merge the resulting cursors data in order to get the sorted list by both there dates (Start and Date) ?

For example:

I am getting this result:

| Date                 | Type        | Location              |
|:---------------------|------------:|:---------------------:|
| 10-Jul-2013 07:05:00 | Random      | Washougal, Washington
| 10-Jul-2013 08:30:00 | Single      | Vancouver, Washington
| 10-Jul-2013 07:30:00 | Multiple    | Vancouver, Washington
| 10-Jul-2013 15:31:00 | Double      | Vancouver, Washington

Where the first two rows are from table X and the last two rows are from table Y in above result.

But I want this result:

| Date                 | Type        | Location              |
|:---------------------|------------:|:---------------------:|
| 10-Jul-2013 07:05:00 | Random      | Washougal, Washington
| 10-Jul-2013 07:30:00 | Multiple    | Vancouver, Washington
| 10-Jul-2013 08:30:00 | Single      | Vancouver, Washington
| 10-Jul-2013 15:31:00 | Double      | Vancouver, Washington

Queries should be:

Cursor1 = Select alpha, beeta, gamma, Remark, id, number from X order by Start ASC


Cursor2 = Select Type, Date, gamma, Location, Obs, number from Y order by Date ASC

After getting result from Cursor1, I am making string html in a loop like this:

String itemList = "";

itemList += "<tr><td align='left' width='28%' style='font-size:8px;padding-left: 15px'>"
    + cursor1.getString(1)
    + "</td><td width='16%' style='font-size:8px'>"
    + cursor1.getString(0)
    + "</td><td width='10%' style='font-size:8px'>"
    + cursor1.getString(2)
    + "</td><td width='20%' style='font-size:8px'>" 
    + "</td><td width='35%'>" + cursor1.getString(3) + "</td></tr>";

Then this itemList is further added to the result from Cursor2 to complete the itemList from both the cursors.

The final itemList is shown as html in the layout.

Upvotes: 1

Views: 1164

Answers (1)

CL.
CL.

Reputation: 180182

You could combine both queries into a single query.

First, ensure that both results have the same number of columns. If not, you might need to add some dummy column(s) to one query.

Then combine the two with UNION ALL:

SELECT alpha, beeta, gamma, Remark, id,   number FROM X
UNION ALL
SELECT Type,  Date,  gamma, Obs,    NULL, number FROM Y

Then pick one column of the entire result that you want to order by. (The column names of the result come from the first query.) In this case, the Start column is not part of the result, so we have to add it (and the Date column is duplicated in the second query, but this is necessary for its values to end up in the result column that is used for sorting):

SELECT alpha, beeta, gamma, Remark, id,   number, Start AS SortThis FROM X
UNION ALL
SELECT Type,  Date,  gamma, Obs,    NULL, number, Date              FROM Y
ORDER BY SortThis

Upvotes: 2

Related Questions