Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: [ask]SQL command act like sort and grep
Top Forums Programming [ask]SQL command act like sort and grep Post 302716629 by gary_w on Tuesday 16th of October 2012 04:56:55 PM
Old 10-16-2012
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.

Code:
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>

These 2 Users Gave Thanks to gary_w For This Post:
14th Adhi
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Sort/Grep Question

Hello all, I have a test file that has the format: ..... O 3.694950 -.895050 1.480000 O 5.485050 .895050 1.480000 Ti -4.590000 4.590000 2.960000 Ti -2.295000 ... (5 Replies)
Discussion started by: aarondesk
5 Replies

2. UNIX for Dummies Questions & Answers

Basic awk question...getting awk to act on $1 of the command itself

I have a script problem that I am not able to solve due my very limited understanding of unix/awk. This is the contents of test.sh awk '{print $1}' From the prompt if I enter: ./test.sh Hello World I would expect to see "Hello" but all I get is a blank line. Only then if I enter "Hello... (2 Replies)
Discussion started by: JasonHamm
2 Replies

3. Shell Programming and Scripting

how to grep sort userids

hello folks i have a file that have data like /test/aa/123 /test/aa/xyz /test/bb/xyz /test/bb/123 in above lines i just wants to grep "aa" and "bb". Thanks, Bash (4 Replies)
Discussion started by: learnbash
4 Replies

4. Shell Programming and Scripting

How to Sort Floating Numbers Using the Sort Command?

Hi to all. I'm trying to sort this with the Unix command sort. user1:12345678:3.5:2.5:8:1:2:3 user2:12345679:4.5:3.5:8:1:3:2 user3:12345687:5.5:2.5:6:1:3:2 user4:12345670:5.5:2.5:5:3:2:1 user5:12345671:2.5:5.5:7:2:3:1 I need to get this: user3:12345687:5.5:2.5:6:1:3:2... (7 Replies)
Discussion started by: daniel.gbaena
7 Replies

5. Shell Programming and Scripting

How to grep the where clause of a SQL?

Hi UNIX Gurus, I want to use extract the where clause of a SQL present in a file. Please suggest me how can I do it. Select * from emp where emp_id>10; cat <file_name> | grep -i "where" returns whole SQL. how can I extract only "where emp_id>10;" Thanks in advance (4 Replies)
Discussion started by: ustechie
4 Replies

6. Shell Programming and Scripting

Is it Possible to sort a list of hexadecimal numbers using "sort" command?

Hello Everybody :) !!!. i have question in mind, is it possible to sort a list of hexadecimal numbers using "sort" command? (9 Replies)
Discussion started by: Kesavan
9 Replies

7. Shell Programming and Scripting

grep from 3 lines and sort

Pseudo name=hdiskpower54 Symmetrix ID=000190101757 Logical device ID=0601 state=alive; policy=SymmOpt; priority=0; queued-IOs=0 ============================================================================== ---------------- Host --------------- - Stor - -- I/O Path - -- Stats --- ### HW... (7 Replies)
Discussion started by: Daniel Gate
7 Replies

8. Homework & Coursework Questions

Grep and Sort

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: 1. Print the number of people that are in the /etc/passwd file with the name of George 2. Sort by name and... (8 Replies)
Discussion started by: Jagst3r21
8 Replies

9. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

10. UNIX for Beginners Questions & Answers

Wget, grep, sort, sed in 1 command/script

Hi, I need to join these statements for efficiency, and without having to make a new directory for each batch. I'm annotating commands below. wget -q -r -l1 URL ^^ can't use -O - here and pipe | to grep because of -r grep -hrio "\b\+@\+\.\{2,4\}\+\b" * > first.txt ^^ Need to grep the output... (14 Replies)
Discussion started by: p1ne
14 Replies

LEARN ABOUT DEBIAN

sql::statement::term

SQL::Statement::Term(3pm)				User Contributed Perl Documentation				 SQL::Statement::Term(3pm)

NAME

       SQL::Statement::Term - base class for all terms

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::Term->new( $owner );
	 # access the value of that term
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::Term is an abstract base class providing the interface for all terms.

INHERITANCE

	 SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores a weak reference to the owner.

   value
       Abstract method which will return the value of the term. Must be overridden by derived classes.

   DESTROY
       Destroys the term and undefines the weak reference to the owner.

NAME

       SQL::Statement::ConstantTerm - term for constant values

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::ConstantTerm->new( $owner, 'foo' );
	 # access the value of that term - returns 'foo'
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::ConstantTerm implements a term which will always return the same constant value.

INHERITANCE

	 SQL::Statement::ConstantTerm
	 ISA SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores the constant to deliver and a weak reference to the owner.

   value
       Returns the specified constant.

NAME

       SQL::Statement::ColumnValue - term for column values

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::ColumnValue->new( $owner, 'id' );
	 # access the value of that term - returns the value of the column 'id'
	 # of the currently active row in $eval
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::ColumnValue implements a term which will return the specified column of the active row.

INHERITANCE

	 SQL::Statement::ColumnValue
	 ISA SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores the column name to deliver and a weak reference to the owner.

   value
       Returns the specified column value.

AUTHOR AND COPYRIGHT

       Copyright (c) 2009,2010 by Jens Rehsack: rehsackATcpan.org

       All rights reserved.

       You may distribute this module under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl
       README file.

perl v5.10.1							    2011-02-01						 SQL::Statement::Term(3pm)
All times are GMT -4. The time now is 01:44 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy