0

I have the following data in Sybase ASE

   id   effectiveDate     lastModificationDate  rateValue    
 -----  ----------------  --------------------  ------------ 
 1      20130627          6/27/2013 3:27:09 AM  0            
 1      20130627          6/27/2013 4:39:10 AM  2.75         
 1      20130627          6/28/2013 3:48:15 AM  0            
 1      20130627          6/28/2013 4:36:43 AM  2.75         
 1      20130628          6/28/2013 3:48:14 AM  0            
 1      20130628          6/28/2013 4:36:42 AM  2.75         
 2      20130628          6/28/2013 4:36:42 AM  .75         
 2      20130628          6/28/2013 3:48:14 AM  0

How do I group it, so that I get only the last row, ie I get the row which has the max lastModificationDate for the same id+effectiveDate.

So output would be : 

 id     effectiveDate     lastModificationDate  value    
 -----  ----------------  --------------------  ------------ 
 1      20130627          6/28/2013 4:36:43 AM  2.75         
 1      20130628          6/28/2013 4:36:42 AM  2.75         
 2      20130628          6/28/2013 4:36:42 AM  .75

Please note that this would be on TSQL (Sybase ASE 15). EDIT: Have changed the data to make it more realistic

Achow
  • 8,600
  • 6
  • 39
  • 49

2 Answers2

0

Try:

SELECT t1.*
FROM Table1 t1
WHERE t1.lastModificationDate  = (SELECT MAX(t2.lastModificationDate)
                                  FROM Table1 t2
                                  WHERE t2.effectiveDate = t1.effectiveDate
                                  AND t2.id = t1.id)

Sybase documentation:

Subqueries can be nested inside the where or having clause of an outer select, insert, update, or delete statement, inside another subquery, or in a select list. Alternatively, you can write many statements that contain subqueries as joins; Adaptive Server processes such statements as joins.

Justin
  • 9,634
  • 6
  • 35
  • 47
  • the only problem with Sybase subqueries is that there can be only 1 column. Think about the situation where the data is like below: the same lastmodificationdate is repeated for another id.Have edited the question to reflect my comment. – Achow Jul 01 '13 at 07:41
0

Another answer avoiding the use of a subquery would be ...

select id, effectiveDate, lastModificationDate, rateValue 
from #mydata
group by id, effectiveDate
having lastModificationDate = max(lastModificationDate)

If I suppose your data is stored in a #mydata temporary table

create table #mydata(
    id                   int      null,
    effectiveDate        char(8)  null,
    lastModificationDate datetime null,
    rateValue            money    null
)

insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130627", "6/27/2013 3:27:09 AM", 0            
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130627", "6/27/2013 4:39:10 AM", 2.75         
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130627", "6/28/2013 3:48:15 AM", 0            
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130627", "6/28/2013 4:36:43 AM", 2.75         
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130628", "6/28/2013 3:48:14 AM", 0            
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 1, "20130628", "6/28/2013 4:36:42 AM", 2.75         
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 2, "20130628", "6/28/2013 4:36:42 AM", .75         
insert into #mydata(id, effectiveDate, lastModificationDate, rateValue) select 2, "20130628", "6/28/2013 3:48:14 AM", 0
user3729932
  • 1