asked on
Paginate with SQL Server 2005 using ROW_NUMBER()
Hi, I would like to paginate a query that has a join ...I can get row numbers with ROW_NUMBER() with a single table like this...(image attached)
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY order_no) AS
rownum,order_no, supplier,product FROM scheme.inwards_goods) AS standard
but have been unable to do it on the query below (which has a join)
can anyone help... select.tiff
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY order_no) AS
rownum,order_no, supplier,product FROM scheme.inwards_goods) AS standard
but have been unable to do it on the query below (which has a join)
can anyone help... select.tiff
SELECT DISTINCT
ig.order_no
,ig.supplier
,ig.warehouse
,ig.product
,ig.unit_code
,ig.long_description
,ig.transaction_ref
,ig.product_long_desc
,ig.qty_ordered
,ig.date_entered
,ig.date_required
,ig.status
,ig.country
,ig.qty_received
,ig.latest_receipt_date
,ig.comments
,ig.po_reference
,ig.memo1
,ig.memo2
,st.lead_time
,st.reorder_days
,st.safety_days
,st.min_stock_level
,st.physical_qty
,st.allocated_qty
,st.back_order_qty
FROM scheme.inwards_goods ig WITH (READUNCOMMITTED)
LEFT OUTER JOIN scheme.stockm st WITH (READUNCOMMITTED) ON (ig.product = st.product)
WHERE ig.warehouse !='dd'
AND ig.warehouse !='d'
AND ig.warehouse !='dx'
ORDER BY ig.date_entered DESC
Microsoft SQL Server 2005PHP
Last Comment
Raja Jegan R
ASKER
Thanks rrjegan17 ...should that query return 1 row (WHERE rnum=1)
it returned 5128 rows, file attached![result]()
it returned 5128 rows, file attached
typo..
It should be between x and y where x and y are the values with which we need to get the records displayed..
Say if you want to display records 1 to 20, then it would be
WHERE rnum between 1 and 20
It should be between x and y where x and y are the values with which we need to get the records displayed..
Say if you want to display records 1 to 20, then it would be
WHERE rnum between 1 and 20
SELECT order_no
,supplier
,warehouse
,product
,unit_code
,long_description
,transaction_ref
,product_long_desc
,qty_ordered
,date_entered
,date_required
,status
,country
,qty_received
,latest_receipt_date
,comments
,po_reference
,memo1
,memo2
,lead_time
,reorder_days
,safety_days
,min_stock_level
,physical_qty
,allocated_qty
,back_order_qty
FROM (
SELECT DISTINCT
ig.order_no
,ig.supplier
,ig.warehouse
,ig.product
,ig.unit_code
,ig.long_description
,ig.transaction_ref
,ig.product_long_desc
,ig.qty_ordered
,ig.date_entered
,ig.date_required
,ig.status
,ig.country
,ig.qty_received
,ig.latest_receipt_date
,ig.comments
,ig.po_reference
,ig.memo1
,ig.memo2
,st.lead_time
,st.reorder_days
,st.safety_days
,st.min_stock_level
,st.physical_qty
,st.allocated_qty
,st.back_order_qty
,ROW_NUMBER() OVER (ORDER BY ig.date_entered DESC) rnum
FROM scheme.inwards_goods ig WITH (READUNCOMMITTED)
LEFT OUTER JOIN scheme.stockm st WITH (READUNCOMMITTED) ON (ig.product = st.product)
WHERE ig.warehouse !='dd'
AND ig.warehouse !='d'
AND ig.warehouse !='dx'
) temp
WHERE rnum between x and y
ASKER
Tried that but still returning lots of records ...15000
SELECT-1-20.gif
SELECT-1-20.gif
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
awesome ...thanks very much
Welcome..
And glad to help you out..
And glad to help you out..
Open in new window