How to write complex Sqlite SELECT statements?

So, allow me to give an example. This is how the table looks like:

+----+------+------+------+
| id | body | date | type |
+----+------+------+------+
| 1  | foo  | 1101 | typ1 |
| 4  | baz  | 1110 | typ1 |
| 3  | bar  | 1115 | typ2 |
| 3  | goo  | 1114 | typ1 |
| 2  | fee  | 1111 | typ2 |
+----+------+------+------+

So I need to prepare a SELECT statement that returns something like this:

+----+------+------+------+
| id | body | date | type |
+----+------+------+------+
| 1  | foo  | 1101 | typ1 |
| 3  | goo  | 1114 | typ1 |
| 3  | bar  | 1115 | typ2 | <- this is here开发者_开发百科 because date is greater than 1114
| 4  | baz  | 1110 | typ1 |
| 2  | fee  | 1111 | typ2 | <- this is here because date is greater than 1110
+----+------+------+------+

What's the sorting logic and what I have to keep in mind?

• id column is really not an ID. As you can see, 3 is repeated and the difference is the type.
• typ1 must be sorted by ID ascending (in this case date does not matter).
• typ2 must be merge into sorted type1 by date (unix time).
• Sentence is passed to an already created method (let's suppose it's object.query("SELECT ... etc.")). So I'm not allowed to do this in multiple steps.

Do you have any ideas? Besides basic stuff like SELECT ... ORDER BY id ASC I haven't been able to think of a way of working this around.

Edit

This is how the sorted table was built:

1. You must order typ1 by ID:

.

+----+------+------+------+
| id | body | date | type |
+----+------+------+------+
| 1  | foo  | 1101 | typ1 |
| 3  | goo  | 1114 | typ1 |
| 4  | baz  | 1110 | typ1 |
+----+------+------+------+

Then we have to put typ2 ordered by date, with this constraints though:

• typ2 must appear below the higher typ1 date which is not greater than the typ2 date.

---

SELECT x.id,x.date, y.body,y.type from table as x left outer join table as y on (x.id=y.id) where x.date >1101 or x.date>1114

---

I guess u want red values ?? am right

---

I think I understand what you are going for. Can you try this and let me know if it does it?

SELECT
    CASE 
        WHEN type='typ1' THEN id 
        WHEN type='typ2' THEN (
            SELECT T1.id
            FROM TheTable AS T1 
            WHERE T1.date=(SELECT MAX(T2.date)
                           FROM TheTable AS T2
                           WHERE T2.type='typ1' AND T2.date<TheTable.date)
                AND T1.type='typ1')
    END AS order_by_value,
    id, body, date, type
FROM TheTable
ORDER BY order_by_value, type, date;

This will be problematic if there are 'typ2' records with dates smaller than the smallest 'typ1' date; otherwise it should work fine.

If you don't want the "order_by_value" column in your result set, subselect from the previous SQL query this way:

SELECT id, body, date, type 
FROM (
SELECT
    CASE 
        WHEN type='typ1' THEN id 
        WHEN type='typ2' THEN (
            SELECT T1.id
            FROM TheTable AS T1 
            WHERE T1.date=(SELECT MAX(T2.date)
                           FROM TheTable AS T2
                           WHERE T2.type='typ1' AND T2.date<TheTable.date)
                AND T1.type='typ1')
    END AS order_by_value,
    id, body, date, type
FROM TheTable
ORDER BY order_by_value, type, date);