If the paid_amount is not NULL or greater than 0 for the greater value of the period_id for that section_id and store_id combination, then the store would get a Y value for checked column. Here is the query I created based on the criteria that is listed above:
select section_id, store_id, paid_amount, period_id, secttion_id + store_id as unique_id
from store_data
where paid_amount is not null and paid_amount > 0
order by store_id, paid_amount desc;
Query above produces data below:
section_id store_id paid_amount period_id unique_id
3604 30545 10000.00 3 30545
3604 30545 5000.00 2 30545
5967 32105 8470.00 3 38072
5967 32105 8470.00 2 38072
1367 46144 23456.00 2 47511
1367 46144 23456.00 3 47511
1367 46144 23456.00 4 47511
1367 46144 23456.00 5 47511
1376 72181 19975.00 2 73557
I need to get only one row of data if there are multiple. For example for unique_id 30545 I only want the row with the greater amount. In that case that should be row with the paid_amount of 10000.00. If row only has one record I only need that row. Is there a simple way to achive this in the Sybase?
since sybase doesn't support window function , here is one way:
select s1.* from store_data s1
join (
select unique_id , max(paid_amount) paid_amount
from store_data s2
where s2.paid_amount is not null and s2.paid_amount > 0
group by s2.unique_id
) s2
on s1.unique_id = s2.unique_id
and s1.paid_amount = s2.paid_amount
however if you still have duplicates , you can narrow the condition :
select s1.* from store_data s1
join (
select unique_id , max(paid_amount) paid_amount, max(period) period
from store_data s2
where s2.paid_amount is not null and s2.paid_amount > 0
group by s2.unique_id
) s2
on s1.unique_id = s2.unique_id
and s1.paid_amount = s2.paid_amount
and s1.period_id = s2.period_id