Login or Register to Ask a Question and Join Our Community


Programming

selecting rows with specific IDs for downstream analysis

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming selecting rows with specific IDs for downstream analysis
# 1  
Old 10-01-2008
selecting rows with specific IDs for downstream analysis
Hi,

I'm working hard on SQL and I came across a hurdle I'm hoping you can help me out with.

I have two tables

table1
headers: chrom start end name score strand
Code:
11   9720685 9720721 U0      0       +
21   9721043 9721079 U0      0       -
1   9721093 9721129 U0      0       +
20   9721485 9721521 U0      0       +
22   9721863 9721899 U0      0       -
21   9721863 9721899 U0      0       -
21   9721871 9721907 U0      0       -
21   9721872 9721908 U0      0       +
5   9722423 9722459 U0      0       +
4   9722434 9722470 U0      0       +

and table 2
headers: chrom start end name score strand
Code:
21   9719765 9749365 DOM1158 61.7441 +
21   10056465        10211865        DOM1159 321.867 +
1   31678598        31859898        DOM1160 73.8664 +
21   32133698        32300198        DOM1161 152.658 +
3   32599898        32910698        DOM1162 435.853 +
21   33177098        33447198        DOM1163 326.637 +
2   33669198        33728398        DOM1164 46.8648 +
21   33824598        34013298        DOM1165 116.821 +
20   34120598        34246398        DOM1166 197.878 +
20   34412898        34505398        DOM1167 98.2537 +

both tables contain 100+ entries and column one has numbers from 1-22.

my objective is to try to run this command

>select count(*) from table1, table2 where table1.start >= table2.start and table1.end <= table2.end;

but I want to get the information for rows matching chrom=21 only.

I've tried group by and then having, but it doesn't seem to work.


Can someone help me out? Thanks.
# 2  
Old 10-01-2008
Not really clear what you're trying to select, post the desired output.

Regards
# 3  
Old 10-01-2008
r u expecting this ?

Code:
select 
          count(*) from table1 a, table2 b 
where 
          a.start >= b.start 
          and a.end <= b.end
          and a.chrom = b.chrom
          and a.chrom = '21' ;

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Size Selecting rows

I have a rather convoluted script that I use to extract information from CSV files: sed '1d' PeakTable.txt | awk -F ',' '!/Ladder/{ if ( $4 > 430 && $4 < 490 && $5 > 45 ) print $2, $5; else print $2, 0 }' | awk '{a+=$2}END{for(i in a){print i, a}}' | sed 's/\(\)\(\) /\10\2 /' | sort | awk... (4 Replies)
Discussion started by: Xterra
4 Replies

2. Shell Programming and Scripting

Help with shell script: selecting rows that have the same values in two columns

Hello, everyone I am beginner for shell programming. I want to print all lines that have the same values in first two columns data: a b 1 2 a a 3 4 b b 5 6 a b 4 6 what I expected is : a a 3 4 b b 5 6 but I searched for one hour in... (2 Replies)
Discussion started by: nengcheng
2 Replies

3. UNIX for Dummies Questions & Answers

Merge two files with common IDs but unequal number of rows

Hi, I have two files that I would like to merge and think that there should be a solution using awk. The files look something like this: file 1 IDX1 IDY1 IDX2 IDY2 IDX3 IDY3 file 2 IDY1 dataA data1 IDY2 dataB data2 IDY3 dataC data3 Desired output IDX1 IDY1 dataA data1 IDX2 ... (5 Replies)
Discussion started by: katie8856
5 Replies

4. Shell Programming and Scripting

Selecting rows from a pipe delimited file based on condition

HI all, I have a simple challenge for you.. I have the following pipe delimited file 2345|98|1809||x|969|0 2345|98|0809||y|0|537 2345|97|9809||x|544|0 2345|97|0909||y|0|651 9685|98|7809||x|321|0 9685|98|7909||y|0|357 9685|98|7809||x|687|0 9685|98|0809||y|0|234 2315|98|0809||x|564|0 ... (2 Replies)
Discussion started by: nithins007
2 Replies

5. UNIX for Dummies Questions & Answers

Removing duplicate rows & selecting only latest date

Gurus, From a file I need to remove duplicate rows based on the first column data but also we need to consider a date column where we need to keep the latest date (13th column). Ex: Input File: Output File: I know how to take out the duplicates but I couldn't figure out... (5 Replies)
Discussion started by: shash
5 Replies

6. Shell Programming and Scripting

Selecting rows based on values in columns

Hi My pipe delimited .txt file contains rows with 10 columns. Can anyone advise how I output to file only those rows with the letters ‘ci' as the first 2 characters in the 3rd column ? Many thanks (4 Replies)
Discussion started by: malts18
4 Replies

7. UNIX for Dummies Questions & Answers

Selecting specific line using awk

Hi, I would like to get the specific line from the file taking specific coloumn as reference. Thanks and Regards (1 Reply)
Discussion started by: kkarthik_kaja
1 Replies

8. Shell Programming and Scripting

Deleting specific rows in large files having rows greater than 100000

Hi Guys, I need help in modifying a large text file containing more than 1-2 lakh rows of data using unix commands. I am quite new to the unix language the text file contains data in a pipe delimited format sdfsdfs sdfsdfsd START_ROW sdfsd|sdfsdfsd|sdfsdfasdf|sdfsadf|sdfasdf... (9 Replies)
Discussion started by: manish2009
9 Replies

9. UNIX for Dummies Questions & Answers

Help selecting some rows with awk

Hi there, I have a text file with several colums separated by "|;#" I need to search the file extracting all columns starting with the value of "1" or "2" saving in a separate file just the first 7 columns of each row maching the criteria, with replacement of the saparators in the nearly created... (2 Replies)
Discussion started by: capnino
2 Replies

10. Shell Programming and Scripting

Selecting rows with a specific criteria

Hi, I want a UNIX command that can filter out rows with certain criteria. The file is tab deliminated. Row one is just a value. Basically what I want to do is select based on the name and character at the end (o). So lets lets say i want a row that has WashU and (o) then it would print... (2 Replies)
Discussion started by: phil_heath
2 Replies
Login or Register to Ask a Question