To see spcific records

[shyamal123]

Hi everybody
I need a query in sqlserver through which i can retrieve the records which are present in the middle part of the table.I mean suppose table has 100 records,then I want to retrive 50 to 70(20 nos of records) records.

[ck9663]

read this:

http://www.sqlteam.com/article/retur...ber-in-a-query


that will help you create a row number using a query... make that query a subquery and put a WHERE outside it to filter your range.

-- CK

[shyamal123]

hello
by your suggestion i got the query to create a column with rownumber .But i can't get the specific record.my query is


select * from(SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id) AS rownumber
FROM employee e order by emp_id asc)where rownumber>1 And rownumber<4

[ck9663]

hello
by your suggestion i got the query to create a column with rownumber .But i can't get the specific record.my query is


select * from(SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id) AS rownumber
FROM employee e order by emp_id asc)where rownumber>1 And rownumber<4

i'll assume that the table name, fields and primary key are correct..

try:

select * from (SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id) AS rownumber
FROM employee e order by emp_id asc) A where rownumber between 1 and 4

select * from(SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id) AS rownumber
FROM employee e order by emp_id asc)where rownumber>1 And rownumber<4
 
 

[amitpatel66]

Check below query, it works:

 
SQL> select empno,ename,(SELECT COUNT(*) FROM EMP WHERE empno <= e.empno) FROM emp e;
 
    EMPNO ENAME                (SELECTCOUNT(*)FROMEMPWHEREEMPNO<=E.EMPNO)
--------- -------------------- ------------------------------------------
        1 BOND                                                          1
        2 AAAA                                                          2
        3 BBBB                                                          3
        4 CCCC                                                          4
        5 TEST                                                          5
        6 TEST                                                          6
       12 KKKK                                                          7
       17 LLLL                                                          8
       20 MMMM                                                          9
      211 ZZZZ                                                         12
      100 Roopa                                                        11
      100 Roopa                                                        11
 
12 rows selected.
 
SQL> SELECT * FROM
  2  (select empno,ename,(SELECT COUNT(*) FROM EMP WHERE empno <= e.empno) cnt FROM emp e ORDER BY cnt) x
  3  WHERE cnt BETWEEN 1 AND 5
  4  /
 
    EMPNO ENAME                      CNT
--------- -------------------- ---------
        1 BOND                         1
        2 AAAA                         2
        3 BBBB                         3
        4 CCCC                         4
        5 TEST                         5
 
 

[shyamal123]

hello
I run the query but it shows the error.Again i remembering you this try is in sqlserver not in oracle.


SELECT * FROM (SELECT job_id,lname,(SELECT COUNT(*) FROM employee WHERE job_id <= e.job_id) cnt FROM employee e ORDER BY cnt) x

WHERE cnt BETWEEN 1 AND 5
->The error message is bellow
Server: Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

[debasisdas]

Try to use UNION & INTERSECT operators.

[Jim Doherty]

Hi everybody
I need a query in sqlserver through which i can retrieve the records which are present in the middle part of the table.I mean suppose table has 100 records,then I want to retrive 50 to 70(20 nos of records) records.

If I am reading you correctly you are basically paging records. Have a look at this interesting blog thread as one method to deal with your question.

http://weblogs.sqlteam.com/jeffs/arc...3/22/1085.aspx

Jim :)