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
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 your original query, the one with GROUP BY?
RE: SQL "Select Query" question.
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL "Select Query" question.
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.
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.
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.
and Thank you r937 for the SQL book.
Have a nice time.