SQL How to Limit BOTTOM x rows (PostgreSQL)

Question

I have a query like:

SELECT 
EXTRACT(WEEK FROM j.updated_at) as "week",  count(j.id)  
FROM  jobs    
WHERE  
EXTRACT(YEAR FROM j.updated_at)=2009   
GROUP BY EXTRACT(WEEK FROM j.updated_at)  
ORDER BY week

Which works fine, but I only want to show the last say 12 weeks, LIMIT 12 works, but only gives me the first twelve and I need the order to be in sequential week order (ie. not reversed) for charting purposes...

Is there a equivalent statement in Postgresql such as Show BOTTOM x items?

Answer 1

SELECT  *
FROM    (
        SELECT  EXTRACT(WEEK FROM j.updated_at) as "week",  count(j.id)  
        FROM    jobs    
        WHERE   EXTRACT(YEAR FROM j.updated_at)=2009   
        GROUP BY
                EXTRACT(WEEK FROM j.updated_at)  
        ORDER BY week DESC
        LIMIT 12
        ) q
ORDER BY
        week ASC

Note that if you have an index on updated_at, you may rewrite this query a little so that it's more efficient:

SELECT  week,
        (
        SELECT  COUNT(j.id)
        FROM    jobs
        WHERE   updated_at >= start_week
                AND updated_at < end_week
        ) 
FROM    (
        SELECT  week,
               '04.01.2009'::DATE - EXTRACT(DOW FROM '04.01.2009'::DATE)::INTEGER + week * 7
               '04.01.2009'::DATE - EXTRACT(DOW FROM '04.01.2009'::DATE)::INTEGER + week * 7 + 7
        FROM   generate_series(42, 53) week
        ) q

Answer 2

If the value for week increases, you could ORDER BY WEEK DESC and then take the TOP 12, right?

UPDATE: You would then have to re-order the 12 resulting rows to have them in chronological order, using ORDER BY Week ASC. (I glossed over the request to have them in ascending order afterwards; Thank you, etlerant!)

Answer 3

SELECT * FROM 
(
    SELECT 
        EXTRACT(WEEK FROM j.updated_at) as "week",
        count(j.id)
    FROM
        jobs    
    WHERE  
        EXTRACT(YEAR FROM j.updated_at) = 2009
    GROUP BY EXTRACT(WEEK FROM j.updated_at)
    ORDER BY week desc limit 12
) as x
ORDER BY week asc;

"as x" is important, as subqueries in from require aliases.