Results 1 to 2 of 2

Thread: qrytest

  1. #1
    Join Date
    Sep 2002
    Posts
    17

    Unanswered: qrytest

    This qry returns rows between the range of 11 to 20. actually with the following condition in the table total 15 records are there but iam retrieving records range between 11 to 20 what i need is the qry should return 11 to 20 records along with total count that is 15 how to write qry plz tell me..

    SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p
    FROM
    (
    SELECT dm.c_device_id a,
    dm.c_device_type_id b,
    dm.c_vendor_name c,
    dm.c_device_version_number d,
    dm.c_network_generation e,
    dm.c_os_name f,
    dm.c_os_version g,
    dm.c_os_type h,
    dm.c_browser_name i,
    dm.c_browser_version j,
    dm.c_oatp_type k,
    dm.c_oatp_connection l,
    dt.c_device_description m,
    dm.c_sw_revision n,
    dm.c_nvram o,
    dm.c_last_modified_date p,
    ROWNUM AS rn
    FROM t_device_master dm,
    t_mobile_device_types dt
    WHERE dm.c_device_type_id = dt.c_device_type_id
    AND dm.c_network_generation = '2.5G'
    ORDER BY dm.c_device_id
    )
    WHERE rn BETWEEN 11 AND 20

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: qrytest

    Originally posted by karraaruna
    This qry returns rows between the range of 11 to 20. actually with the following condition in the table total 15 records are there but iam retrieving records range between 11 to 20 what i need is the qry should return 11 to 20 records along with total count that is 15 how to write qry plz tell me..

    SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p
    FROM
    (
    SELECT dm.c_device_id a,
    dm.c_device_type_id b,
    dm.c_vendor_name c,
    dm.c_device_version_number d,
    dm.c_network_generation e,
    dm.c_os_name f,
    dm.c_os_version g,
    dm.c_os_type h,
    dm.c_browser_name i,
    dm.c_browser_version j,
    dm.c_oatp_type k,
    dm.c_oatp_connection l,
    dt.c_device_description m,
    dm.c_sw_revision n,
    dm.c_nvram o,
    dm.c_last_modified_date p,
    ROWNUM AS rn
    FROM t_device_master dm,
    t_mobile_device_types dt
    WHERE dm.c_device_type_id = dt.c_device_type_id
    AND dm.c_network_generation = '2.5G'
    ORDER BY dm.c_device_id
    )
    WHERE rn BETWEEN 11 AND 20
    This will give you the total count alongside each record selected:

    SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,cnt
    FROM
    (
    SELECT dm.c_device_id a,
    dm.c_device_type_id b,
    dm.c_vendor_name c,
    dm.c_device_version_number d,
    dm.c_network_generation e,
    dm.c_os_name f,
    dm.c_os_version g,
    dm.c_os_type h,
    dm.c_browser_name i,
    dm.c_browser_version j,
    dm.c_oatp_type k,
    dm.c_oatp_connection l,
    dt.c_device_description m,
    dm.c_sw_revision n,
    dm.c_nvram o,
    dm.c_last_modified_date p,
    ROWNUM AS rn,
    COUNT(*) OVER () AS cnt
    FROM t_device_master dm,
    t_mobile_device_types dt
    WHERE dm.c_device_type_id = dt.c_device_type_id
    AND dm.c_network_generation = '2.5G'
    ORDER BY dm.c_device_id
    )
    WHERE rn BETWEEN 11 AND 20

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •