Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

SQL - Group and Get Top 1

I have a sql statement that returns the below data

AccTID     PKID    RTID            BeginDate                     EndDate  
123123      888999      1      2014-05-01 00:00:00      2014-05-31 23:59:00
123123      888999      1      2014-06-01 00:00:00      2014-06-30 23:59:00
123123      888999      1      2014-07-01 00:00:00      2014-07-31 23:59:00
123123      888999      1      2015-04-01 00:00:00      2015-04-30 23:59:00
123123      888999      3      2014-05-01 00:00:00      2014-05-31 23:59:00
123123      888999      3      2014-06-01 00:00:00      2014-06-30 23:59:00
123123      888999      3      2015-01-01 00:00:00      2015-01-31 23:59:00
123123      888999      3      2015-02-01 00:00:00      2015-02-28 23:59:00
123123      888999      3      2015-04-01 00:00:00      2015-04-30 23:59:00

The sql statement is as so.
Select DISTINCT t1.AccTID, t2.pkid, t6.rtid, t5.beginDate, t5.enddate
From table1 t1
inner join table2 t2 on f1.AccTID = f2.AccTID
inner join table3 t3 on f2.esid = f3.esid
inner join table4 t4 on e.crid = en.crid
inner join table5 t5 on c.crid = e.crid
inner join table6 t6 on t6.cbid = t5.cbid
where t1.id = 123123
and t2.acctid = 888999
and (t6.rtid = 1 or t6.rtid = 3)
order by f1.AccTID, f2.pkid, t6.rtid, t5.beginDate, t5.enddate desc

Open in new window


I need to get is one record.  The max end date sorted by 1 then 3.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial