Find missing rows using SQL

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

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

Answer 5

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).