SQL "Select Query" question. 1

[arfasih]

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

 

[JarlH]

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

Whats your original query, the one with GROUP BY?

 

[r937]

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

 

[arfasih]

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

 

[r937]

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

 

[JarlH]

[tt]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
[/tt]

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!

 

[arfasih]

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