SELECT DISTINCTig.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_qtyFROM 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
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_qtyFROM (SELECT DISTINCTig.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 ( PARTITION BY ig.order_no ORDER BY ig.date_entered DESC) rnumFROM 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') tempWHERE rnum = 1
Thanks rrjegan17 ...should that query return 1 row (WHERE rnum=1)
it returned 5128 rows, file attached
Raja Jegan R
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
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_qtyFROM (SELECT DISTINCTig.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) rnumFROM 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') tempWHERE rnum between x and y
Open in new window