Distinct one column return selected columns and order by date desc using sybase

Hi Guys i have a problem on how im going to distinct single column and return selected column

Ac_no ord_status order_no
12334    PL         1
12334    ML         2
12334    CL         3
64543    PL         1
65778    JL         6
83887    CL         4
83887    KL         3

Ac_no ord_statu sorder_no
12334    CL        3
64543    PL        1
65778    JL        6
83887    CL        4

i want to see that result

here is my sample or code but unfortunately the code didnt work in sybase 1.2.0.637

SELECT Ac_no, ord_status, order_no select *, ROW_NUMBER() OVER (PARTITION BY Ac_no order by ord_status)rm from wo_order)x where x = 1

Solution

It appears that you want to display, for each Ac_no group of records, the single record having the lowest ord_status. You were on the right track, but you need to restrict the subquery using the alias you defined for the row number:

SELECT Ac_no, ord_status, order_no
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Ac_no ORDER BY ord_status) rn
    FROM wo_order
) t
WHERE rn = 1;

Here is a version which should run on your version of Sybase, even without using ROW_NUMBER:

SELECT w1.Ac_no, w1.ord_status, w1.order_no
FROM wo_order w1
INNER JOIN
(
    SELECT Ac_no, MIN(ord_status) AS min_ord_status
    FROM wo_order
    GROUP BY Ac_no
) w2
    ON w1.Ac_no = w2.Ac_no AND
       w1.ord_status = w2.min_ord_status;