×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL "Select Query" question.

SQL "Select Query" question.

SQL "Select Query" question.

(OP)
Hello
I have a Table with two field and one million row.
List of this table has been generated by SQL "Group By" command.

it's looks like below table,
now, I want to select those rows of the table which the difference between the value of each row and previous row in second fields (and same group) are greater than 5.


Input  Output ???
------|-------

A 2    A 2    
A 3
A 6    
A 20    A 20
A 37    A 37

Z 38    Z 38
Z 21    
Z 70    Z 70

D 18    D 18
D 19    
D 22    
D 90    D 90
D 125    D 125

Whould you please help me to find a solution by SQL.

Many thanks

RE: SQL "Select Query" question.

Whats the difference between 'D 18' and 'Z 70'? And between 'A 20' and null?

Whats your original query, the one with GROUP BY?
 

RE: SQL "Select Query" question.

also, which dbms is this?  because the solution in ANSI SQL (the forum you posted in) might not work for you

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: SQL "Select Query" question.

(OP)
SELECT name, value_ INTO Table2 FROM Table1 GROUP BY name, value_

Table2
--name--|--value--
A          2       
A          3
A          6    
A          20   
A          37    
Z          38    
Z          41    
Z          70    
D          18    
D          19    
D          22    
D          90    
D          125  

now, I need another SQL query to extract the below list from Table2:
I want to select those rows of the table which the difference between the value of each row and previous row in second field (and same group) are greater than 5.

--name--|--value--
A          2       
A          20   
A          37    
Z          38    
Z          70    
D          18    
D          90    
D          125

We have selected (A 2), (Z 38) and (D 18) because they are in the begining of each group and after that we check the difference between the rows.

there was a mistake in my first Post. (Z 21) instead of (Z 41)

Sorry, I didn't explain it well before.
Thank you

RE: SQL "Select Query" question.

SELECT INTO ??

are you using microsoft sql server?

(this is the ANSI SQL forum)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: SQL "Select Query" question.

SELECT name, value
FROM table2 t2
WHERE (SELECT MAX(value) FROM table2   
       WHERE name = t2.name AND value < t2.value) < t2.value - 5
UNION
SELECT name, min(value)
FROM table2
GROUP BY name
ORDER BY name, value


The first part of the union finds the rows where the difference is > 5. The second part finds all the "minimum" rows.


Note that VALUE is a reserved word in ANSI/ISO SQL. If you are going to use that column name, you may run into problems when your DBMS is upgraded to a newer version!

RE: SQL "Select Query" question.

(OP)
Thank you JarlH for your answer :)
and Thank you r937 for the SQL book.
Have a nice time.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close