Sql Selecting "Window" Around Particular Row

SQL Selecting Window Around Particular Row

Probably could just use a UNION, and then trim off the extra results in the procedural code that displays the results (as this will return 20 rows in the non-edge cases):

(SELECT 
     * 
FROM photos
   WHERE ID < #current_id#
   ORDER BY ID DESC LIMIT 10)
UNION
  (SELECT *
   FROM photos
   WHERE ID >= #current_id#
   ORDER BY ID ASC LIMIT 10)
ORDER BY ID ASC

EDIT: Increased limit to 10 on both sides of the UNION, as suggested by le dorfier.

EDIT 2: Modified to better reflect final implementation, as suggested by Dominic.

SQL: Select certain rows with window functions in MySQL/MariaDB

I think the simplest approach is not exists:

select d.*
from dsort d
where not exists (
    select 1
    from dsort d1
    where d.dfrom > d1.dfrom  and d.dfrom < d1.dto
)

SQL - How to select x number of rows prior to a specific row

Hmmm . . . You can use lead():

select t.*
from (select t.*,
             lead(event) over (partition by user_id order by ts) as next_event,
             lead(event, 2) over (partition by user_id order by ts) as next_event2
      from t
     ) t
where 'sleep' in (event, next_event, next_event2);

Note: This only returns rows that are in the data. If you need for rows to be generated additional logic is needed.

EDIT:

You can actually generalize this:

select t.*
from (select t.*,
             sum(case when event = 'sleep') over (partition by user_id order by ts rows between current row and 2 following) as cnt_sleep
      from t
     ) t
where cnt_sleep > 0;

This counts the number of "sleep"s in the next n rows (well, n - 1). It returns a row if "sleep" is found in any of them.

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

select a specific row for each group according to a custom logic

You may try to use first_value() window function with proper custom order by in the window definition. Yet using distinct on with custom ordering solves your problem easier and better. You can still use min() for the price.

select distinct on (id_2) * 
from test_table 
order by id_2, 
  case store when 'a' then 1 when 'c' then 2 end nulls last;

Please note the second (case) expression in the order by clause. It is modeling the rule "If there is a row with store = 'a' in the same id_2 group of rows then take it, else if there is a row with store = 'c' in it then take it, else take any row of the group".

select t.*
from t
where t.id = '1144QQT' and t.keyword <> 'delete';

select t.*
from t
where t.id = '1144QQT' and
      t.id < (select min(t2.date)
              from t t2
              where t2.id = t.id and
                    t2.keyword <> 'delete'
             );

select t.*
from (select t.*,
             min(date) filter (where keyword = 'delete') over (partition by id) as min_delete_date
      where t.id = '1144QQT' 
     ) t
where date < min_delete_date;

select t.*
from (select t.*,
             min(case when type = 'event2' then sequence end) over (partition by id) as event2_sequence
      from t
     ) t
where event2_sequence is null or
      sequence <= event2_sequence;

select t.*
from t
where t.sequence <= (select min(t2.sequence)
                     from t t2
                     where t2.id = t.id and t2.type = 'event2'
                    );

select t.*
from t
where t.sequence <= (select coalesce(min(case when t2.type = 'event2' then t2.sequence end),
                                     max(t2.sequence)
                                    )
                     from t t2
                     where t2.id = t.id
                    );