在SQLite3中查看

Question

I have little troubles with my SQL code. I have following table:

CREATE TABLE post (
    post INTEGER,
    content TEXT,
    time INTEGER,
    owner TEXT,
    thread TEXT, 
    pharse TEXT);

Of course the table have default values, options, etc. owner has a foreign key to another table with users. Column thread could have multiple same values.

I want to build a view like this:

CREATE VIEW thread AS
SELECT 
    thread,
    MIN(time) AS creation,
    owner,
    pharse,
    MAX(time) AS last,
    COUNT(post) as count
FROM post
GROUP BY thread
ORDER BY time DESC;

thread group my view, creation is a creation time of first post, owner is a creator of first post, pharse is a pharse of first post, last is a creation time of last post, and count is a count of all posts with a same thread value.

And everything would be fine except the MAX(time) AS last, this column forces the view to show the owner of the last post, when I want to see the owner of first. When I kick off that line from code everything works fine, but I need it to sort my view, and this is only cause I want to keep it - for sorting.

Order of columns has no mean for me. I'm using this queries in PHP if it has any importance.

If there would be another way or somebody could tell me how to fix my code I will be pleased.

Answer 1

To prevent the MAX() from influencing the rest of the query, you have to move it into a subquery:

SELECT thread,
       MIN(time) AS creation,
       owner,
       pharse,
       (SELECT MAX(time)
        FROM post AS p2
        WHERE p2.thread = post.thread
       ) AS last,
       COUNT(post) as count
FROM post
GROUP BY thread