0

I have a table with 3 fields namely id,filename and prev_id used for storing uploaded files

Id |  filename | prev_id
---------------------------
1  |  file1    | NULL
2  |  file2    | 1
3  |  file3    | 2
4  |  file4    | NULL
5  |  file5    | 4
6  |  file6    | 5

file3 is the latest uploaded file while others are the previous one noted with prev_id. I want a query to list the previous files of file3. Like wise another newly uploaded file is file6 . So when

http://www.sqlfiddle.com/#!9/0e88c0/1

Expected Output

List of previous files of file3

Id |  filename 
------------
1  |  file1    
2  |  file2

List of previous files of file6

Id |  filename 
------------
4  |  file4    
5  |  file5
Preethi
  • 47
  • 11

3 Answers3

1

based on the (original) sample you provide 

id  fname   prev_id
1   file1   (null)
2   file2   1
3   file3   2
4   file4   (null)

You could use a self join eg: 

select a.* 
from test_table a
inner join test_table b on b.fname ='file3' 
    and a.prev_id  <= b.prev_id

http://www.sqlfiddle.com/#!9/9ec606/21

the a table retunn the prev_id for file3 and the b table return the valud that satisfied your condition for query

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • When using the above query , i got following response Id | filename ------------ 1 | file2 2 | file3 – Preethi Apr 16 '18 at 10:45
  • change the question after an answer is not fair .. .. if you have change idea .. you should post a new question don't invalidate the answer provided .. – ScaisEdge Apr 16 '18 at 11:00
  • 1
    I didnt change the idea of question . i just add two more rows in the table to check the query will satisfied or not – Preethi Apr 16 '18 at 11:11
1

This will provide you result including file3

select t1.id, t1.fname, @pv := t1.prev_id prev_id
from (select * from test_table order by id desc) t1
join (select @pv := 3) tmp  // 3 is the id of file3
where t1.id = @pv;

Ref : https://stackoverflow.com/a/24901882/8317643

UPDATE

Result without file3

select (@pv := t1.prev_id) id, t1.fname
from (select * from test_table order by id desc) t1
join (
     select @pv := (
            select t4.prev_id
            from test_table t4
            where t4.id = 3
          )
) tmp
where t1.id = @pv;
DEarTh
  • 975
  • 1
  • 7
  • 18
0
select * from file where id < 14 ;

First way it will list all the files which have id less than file3 as you said they are already sorted.

Other way to do the same thing is : ( if the file uploaded has many previous records you can limit them)

select * from file where id in (
            select id from file where id < 14
     ) order by id DESC limit 1;
DEarTh
  • 975
  • 1
  • 7
  • 18
rawatsandeep1989
  • 95
  • 1
  • 7