[ask]SQL command act like sort and grep

Applications Programming
1

for example, I have a text file in random content inside, maybe something like this.

234234
54654
123134
467456
24234234
7867867
23424
568567

if I run this command

cat "filename.txt" | sort -n | grep "^467456$" -A 1 -B 1

the result is

234234
467456
568567

is it possible to do this command in sql query?
the query might be like this.

2

Have a look at this. Using union merges the queries together and sorts them. Logically this is not what you asked for though. Your example was getting a row on either side of the target value. This example gets the next highest and next lowest value relative to the target value. By definition the order of rows do not matter in a relational database table, so one would not really select a row on either side of a value in practice. Hopefully someone with better SQL skills can make this more efficient.

SQL> create table test (
  2  idx number);

Table created.

SQL> insert into test values (234234);

1 row created.

SQL> insert into test values (54654);

1 row created.

SQL> insert into test values (123134);

1 row created.

SQL> insert into test values (467456);

1 row created.

SQL> insert into test values (24234234);

1 row created.

SQL> insert into test values (7867867);

1 row created.

SQL> insert into test values (23424);

1 row created.

SQL> insert into test values (568567);

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  select idx
  2  from test
  3  where idx=467456
  4  union
  5  select max(idx)
  6  from test
  7  where idx < 467456
  8  union
  9  select min(idx)
 10  from test
 11* where idx > 467456
SQL> /

       IDX
----------
    234234
    467456
    568567

SQL>
2 Likes
3

SQL is not a natural place for order and numbering. You can add order with 'order by' but numbering is a bit server-specific. But there are ways. Suppose your text file is in a one column table file_name with a numeric column named idx, and all values are unique. You can select the count of rows where idx < 467456, so you know its nominal 0-based number in an ordered listing. You can now use that number to select from file_name where the count of rows less minus the first subquery is 'between -1 and 1':

select
  b.idx
 from file_name b
 where ( (
      select count(c.*) from file_name c where idx < 467456
     ) - (
      select count(a.*) from file_name a where a.idx < b.idx
   ) ) between -1 and 1
1 Like