To calculate min and max of record for huge volume of record

Question

My problem is to get max and minimum date for ABC. I have to do this for around 200 000 records which takes more time. How to improve the performance

ROW_ID     DATE                  C     value
----------------------------------------------  
1    2012-08-01 00:00:00.0  ABC      87  
2    2012-09-01 00:00:00.0  ABC      87   
3    2012-10-01 00:00:00.0  ABC      87   
4    2012-11-01 00:00:00.0  ABC      87   
5    2012-12-01 00:00:00.0  ABC      87   
6    2013-01-01 00:00:00.0  CBA      87   
7    2013-02-01 00:00:00.0  ABC      87   
8    2013-03-01 00:00:00.0  ABC      87

Taryn · Accepted Answer · 2013-05-28T18:03:39.873

2

You should be able to do this easily using something like:

select c,
  min(date) min_date,
  max(date) max_date
from yt
where c='ABC'
group by c;

See SQL Fiddle with Demo.

Edit, since you are attempting to use this data to update another table in Sybase you have a few options. Sybase does not allow derived tables in UPDATE statements so I would suggest using a temp table to get the min/max date for each c and then use this table in your UPDATE with JOIN:

select c,
  min(date) min_date,
  max(date) max_date
into #temp2
from yt
where c='ABC'
group by c;

update t
set t.min_date = t1.min_date,
  t.max_date = t1.max_date
from temp t
inner join #temp2 t1
  on t.c = t1.c;

See SQL Fiddle with Demo

edited May 28 '13 at 18:03

answered May 28 '13 at 15:16

Taryn

242,637
56
362
405

Thanks bluefeet. But problem is i have to use this in a update query. the update query doesnt support group by clause. – antosnowin May 28 '13 at 16:59
@antosnowin What are you updating? Please edit your OP with the exact details of your requirements – Taryn May 28 '13 at 17:00
I ll be updating the min and max value to another table based on 'C' value..I need the same logic in update query. – antosnowin May 28 '13 at 17:05
@antosnowin Can you edit your question with those details? The table details, data, etc – Taryn May 28 '13 at 17:06
The below query is taking lot of time , i need to have the same logic in optimized way. UPDATE #TEMP SET MIN_DATE = (SELECT MIN(DATE) FROM XYZ WHERE TMP.C = C AND TMP.Value =Value ) ,MAX_DATE =(SELECT MAX(DATE) FROM XYZ WHERE TMP.C = C AND TMP.Value =Value ) FROM #TEMP TMP – antosnowin May 28 '13 at 17:14
@antosnowin Sybase doesn't allow derived tables, so you will have to look at using a temp table to get the max/min value for each `C`, then join that temp table to the table you want to update -- see this demo -- http://sqlfiddle.com/#!3/b9f98/2. If it works, then I will post it as an answer here. – Taryn May 28 '13 at 17:41
thanks a lot bluefeet. i cud see huge improvement in terms of performance. – antosnowin May 28 '13 at 18:01

To calculate min and max of record for huge volume of record

1 Answers1