Selecting the row before the resultset in sqlite

Question

Hi guys. so lets say i have a table called data

data

id | date      | name
---------------------    
1  | 19/8/2010 | John    
2  | 19/8/2010 | Mary    
3  | 20/8/2010 | Peter    
4  | 20/8/2010 | Bert     
5  | 20/8/2010 | Ernie 

---

if i do a select statement like this

SELECT * FROM data where date = '20/8/2010';

is it possible to edit this select statement to take the row before it as well. in this case it returns me row number 3 - 5. but is is possible to edit the sqlstatement in any way to select row 2 as well?

Answer 1

If you plainly want to select a row before the actual set of rows then i am afraid there is no such method

I am not sure what you are trying to achieve but you can not do that.

Answer 2

Maybe this is close to what you want:

 SELECT * FROM data where date <= '20/8/2010' order by date desc;

and then stop reading from the result after you have the first row before August 20.

Answer 3

Select d.* 
from data d, 
(SELECT min(id) As LowestID FROM data where date = '20/8/2010') m
where d.id=m.LowestID-1

Answer 4

Once the above query result is available in the current list then you can access any of the returned rows with a cursor.

Answer 5

I don't know anything about SQLITE, but for SQL Server, for example, I might do something like:

SELECT * FROM (SELECT TOP 1 * FROM data where date < '20/8/2010' ORDER BY date DESC, id DESC)
UNION
SELECT * FROM data where date = '20/8/2010'
;

This assumes that your criteria for "the row before" is the row with the largest date < the date you queried for, and if more than one row have that date, then grab the one with the max id.

Answer 6

Adapting @SteveCav's solution to UNION with the original query:

SELECT d.* 
FROM   data d
JOIN   (
          SELECT MIN(id) lowest_id
          FROM   data 
          WHERE  date = '20/8/2010'
       ) dt ON (dt.lowest_id - 1 = d.id)
UNION
SELECT * 
FROM   data 
WHERE  date = '20/8/2010';

Test case (tested in sqlite 3):

CREATE TABLE data (id int, date date, name varchar(10));

INSERT INTO data VALUES (1, '19/8/2010', 'John');
INSERT INTO data VALUES (2, '19/8/2010', 'Mary');
INSERT INTO data VALUES (3, '20/8/2010', 'Peter');
INSERT INTO data VALUES (4, '20/8/2010', 'Bert');
INSERT INTO data VALUES (5, '20/8/2010', 'Ernie');

Result:

id          date        name      
----------  ----------  ----------
2           19/8/2010   Mary      
3           20/8/2010   Peter     
4           20/8/2010   Bert      
5           20/8/2010   Ernie 

This is assuming that by "the row before", you intend the row with the previous id value.

Answer 7

Select Id, [Date], Name
From MyTable
Where [Date] = '2010-08-20'
Union All
Select Id, [Date], Name
From MyTable
Where Id = (
            Select Max(T2.Id)
            From MyTable As T2
            Where T2.[Date] < '2010-08-20'
            )