I have an SQL table as such
name | engins| frank
---------------------------+-------+------
John Smith | 8422| 854
(1 rows)
And need to make a query such that only return the row john smith when engins is more than 2000
I'd try a query like this (PostgreSQL 8.4+)
WITH TT AS (
SELECT start, stop, LAG(stop) OVER(ORDER BY stop ASC) AS lastStop
FROM yourtable
)
SELECT lastStop as start, start as stop
FROM TT
WHERE lastStop <> start;
LAG() selects the value from the previous row.
Create a table with one column year and populate it with all valid years. Now join where year between start and stop. There is still a little work left but you should be able to get it from there.
Use a FULL OUTER JOIN, something like this:
SELECT
*
FROM
yourtable AS a
FULL OUTER JOIN yourtable AS b ON a.stop = b.start
WHERE
a.stop IS NULL
OR
b.start IS NULL
Don't know if this syntax works in mysql, but I use it in tsql.
Use the code you wrote to get the stop years that aren't in the start column and vice versa
and add where clauses like
where start> (select top 1 start from table order by start)
and where stop < (select top 1 stop from table order by stop desc)
SELECT *
FROM (
SELECT *, LAG(stop) OVER (ORDER BY start) AS start_anarchy, start AS stop_anarchy
FROM (
SELECT *
FROM reigns
UNION ALL
VALUES (NULL::INTEGER, NULL::INTEGER)
) q
) q
WHERE start_anarchy IS DISTINCT FROM stop_anarchy
This will also show the years before the first ruler and after the last one.
From the original table again i'm trying to get an output table where i would have the centuries in the first column and in the second column the average time of all the people in charge in that century.
SELECT TRUNC(start - 1, -2) AS century, AVG(stop - start) AS avg_reign
FROM q
GROUP BY
TRUNC(start - 1, -2)
The centuries are calculated according the the start of the reign, and 00 years are the last years of previous centuries (e. g. Constantine II would be a IX century king).
