SQL How to Constrain BOTTOM x Strings (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

What works, but I want to show the last word of 12 weeks, LIMIT 12 works, but gives me the first twelve, and I need the order to be in the sequential order of the week (i.e. not canceled) for the purpose of building diagrams ...

Is there an equivalent statement in Postgresql like 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

Please note that if you have an index on updated_at, you can rewrite this query a bit to make it 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 week value increases, you can ORDER WEEK DESC, and then take TOP 12, right?

UPDATE: 12 , , ORDER BY Week ASC. ( , , , !)

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 because the subqueries from the request require aliases.