How to return greater value of the period for unique id combination?

Question

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?

eshirvana · Accepted Answer · 2021-06-16T18:16:46.753

1

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

edited Jun 16 '21 at 18:16

answered Jun 16 '21 at 17:41

eshirvana

23,227
3
22
38

I assume that where clause should be applied as well. Correct? – espresso_coffee Jun 16 '21 at 17:52
@espresso_coffee yes, – eshirvana Jun 16 '21 at 17:54
I tried example above, but I'm still getting two records for the same store_id instead of one. I'm not sure if the example above is accurate. – espresso_coffee Jun 16 '21 at 18:10
Yes, they do have same amount. – espresso_coffee Jun 16 '21 at 18:12
first , my subquery was based on store_id , but now I noticed you wanted per unique_id , so see updated query , – eshirvana Jun 16 '21 at 18:18
second if you still getting duplicates , see my second query , if you still have duplicate add more condition like what I did , – eshirvana Jun 16 '21 at 18:20
1

The original query was joining the paid_amount column and that was causing the result set with more than one row. After I applied period_id in the join clause the data was accurate. That filter eliminated the duplicates. – espresso_coffee Jun 16 '21 at 18:37

How to return greater value of the period for unique id combination?

1 Answers1