How To Find Odd Numbers In Sql
- Remove From My Forums
-
Question
-
Plz explain how below worked fifty-fifty and odd query
2 ) why used subquery after from and which time nosotros tin can use(what fourth dimension of out put)
fifty-fifty
select * FROM (SELECT ROW_NUMBER() OVER(ORDER BY stud_id) as row ,grno,stud_id from stud_Enrollment) d
WHERE d.row %2=0odd
select * FROM (SELECT ROW_NUMBER() OVER(ORDER BY stud_id) as row ,grno,stud_id from stud_Enrollment) d
WHERE d.row %two=i- Changed type Sunday, November 2, 2014 3:28 PM
Answers
-
Row_Number function returns the sequential number of a row
(By using partition of a result set up, starting at 1 for the outset row in each partition)>> why used subquery after from and which time we can use(what fourth dimension of out put)
When we demand sequntial numbers, we can use row_number office. Put it into derived tabular array and employ this derived table after FROM clause (Same way it is used in your query)
CREATE Table stud_Enrollment ( grno int, stud_id int, stud_name varchar(xx) ) INSERT INTO stud_Enrollment VALUES (101, 511, 'Dheeraj'), (112, 521, 'Vaibhav'), (132, 522, 'Lalit'), (124, 564, 'Amogh'), (143, 598, 'Sushrut') SELECT * FROM stud_Enrollment -- Result of your table --grno stud_id stud_name ------------- ----------- -------------------- --101 511 Dheeraj --112 521 Vaibhav --132 522 Lalit --124 564 Amogh --143 598 Sushrut -- Now we need to observe out the rows which are at even position, ie row should be at position of 0,2,4,vi,eight etc.. -- Only we don't accept sequential number hither in your table consequence -- So we tin can create 1 new cavalcade by using row_number function SELECT ROW_NUMBER() OVER (ORDER Past stud_id) AS row, --> additiona "row" column grno, stud_id, stud_name FROM stud_Enrollment -- Nosotros got "row" cavalcade in below output --row grno stud_id stud_name ---------------------- ----------- ----------- -------------------- --1 101 511 Dheeraj --2 112 521 Vaibhav --3 132 522 Lalit --4 124 564 Amogh --v 143 598 Sushrut -- Now above tabular array is used after FROM clause. It uses row cavalcade in WHERE function SELECT * FROM (SELECT ROW_NUMBER() OVER (Society By stud_id) AS row, grno, stud_id, stud_name FROM stud_Enrollment) d WHERE d.row % ii = 0 --row grno stud_id stud_name ---------------------- ----------- ----------- -------------------- --2 112 521 Vaibhav --iv 124 564 Amogh
-Vaibhav Chaudhari
- Edited by Vaibhav.Chaudhari Sunday, November ii, 2014 three:53 PM
- Marked as answer by tusharshinde Monday, November 3, 2014 7:06 AM
-
Plz explicate how below worked even and odd query
2 ) why used subquery after from and which time nosotros tin use(what time of out put)
even
select * FROM (SELECT ROW_NUMBER() OVER(ORDER Past stud_id) every bit row ,grno,stud_id from stud_Enrollment) d
WHERE d.row %2=0odd
select * FROM (SELECT ROW_NUMBER() OVER(ORDER BY stud_id) as row ,grno,stud_id from stud_Enrollment) d
WHERE d.row %2=oneIts not subquery just its called derived table
The reason why you need a derived tabular array hither is to generate the additional column row which will requite the sequential number using which y'all segregate even and odd rows
If y'all want to use the boosted column in the where every bit a filter and then y'all need to grade a derived tabular array out of it just like what y'all've posted.
Please Mark This As Answer if information technology solved your outcome
Please Marker This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Folio
My Personal Blog
My Facebook Page- Marked as respond by tusharshinde Monday, November 3, 2014 seven:06 AM
How To Find Odd Numbers In Sql,
Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/43e3c78c-4d21-42c4-a643-288373900662/how-to-work-even-and-odd-number-query?forum=transactsql
Posted by: gagnefloore45.blogspot.com
0 Response to "How To Find Odd Numbers In Sql"
Post a Comment