SQL N results before and after the matching row

Question

3.00/5 (1 vote)

See more:

Hello!

I'm having trouble with an SQL statement. I find duplicate entrys in the database and i want to search for each entry individualy + 5 rows before and after the result. I dont know how to make such a statement, could anybody help?

p.s.: I dont have ID's on the rows.

Posted 31-Aug-13 8:54am

tokano

Add a Solution

Comments

Zoltán Zörgő 31-Aug-13 15:25pm

Not clear. What exactly do you want? Please give a comprehensive example!

tokano 31-Aug-13 15:27pm

I'm having like a database of files and they are organized by date and time. Lets say i'm about to search for a entry and i found it. I want the SQL to print out 5 rows before this entry and 5 rows after... I hope u understand me better now

Zoltán Zörgő 31-Aug-13 15:32pm

And what about the duplicates you mentioned?
And what if there are more 10 identical datetime values before (or after) the picked one?

tokano 31-Aug-13 15:48pm

There will not be more. Lets just say i have a database of 50 records. I find a record at row 30. I want now to print out rows from 25-35.

So 5 rows before and after the find row

Zoltán Zörgő 31-Aug-13 15:57pm

Ok, and you need this in SQLite?

tokano 31-Aug-13 16:10pm

Yes!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2013-08-31T10:11:00

Solution 1

Well, supposing you have dates and sqlite, as you mentioned before here is a possible approach:

SQL

select * from
(select * from d
where d <= '2013.01.07'
order by d desc
limit 6)
union
select * from
(select * from d
where d >= '2013.01.07'
order by d
limit 6)

It will select the eleven dates:

It is not the cleanest, but it works.

Posted 31-Aug-13 10:11am

Zoltán Zörgő

Comments

tokano 31-Aug-13 17:13pm

This seems to work, but i think the duplicates you mentioned before make a problem :/

Zoltán Zörgő 31-Aug-13 17:28pm

You wrote you don't have any id. Well, you have by default a rowid. That might help you.


select * from
(select rowid, * from d
where d <= '2013.01.07'
order by d desc
limit 6) 
union
select * from
(select rowid, * from d
where d >= '2013.01.07'
order by d
limit 6) 
order by d

tokano 31-Aug-13 20:05pm

U are a genious! This rowid helped and i have increased the limit for better results. How can i return back to you, if i have other-related question about this? This problem here is solved and you seem to know alot about SQL alot.

Thnx for helping!

Zoltán Zörgő 1-Sep-13 16:12pm

I am glad I helped. I suggest you post your questions in the forum. The community is ready to help you.