asked on

MS SQL Select Maximum Date Row Only

Sir, I have below query:

select       Transno,
      CargoType,
      CntrSize,
      CntrType,
      Destination,
      amount,
      SD=Max(startingDate),
      ED=EndingDate
from MTImpAgentRateDet
where transno='008'
group by transno,
      cargotype,
      CntrSize,
      CntrType,
      Destination,
      amount
left outer join MTImpAgentCost b on a.sn=b.sn
where a.transno='008'

Result
--------

008      General      20'      DC      Kabul      1600      2005-11-24 00:00:00.000      2005-12-12 00:00:00.000
008      General      20'      DC      Kabul      1900      2005-09-17 00:00:00.000      2005-11-07 00:00:00.000
008      General      20'      DC      Kabul      1930      2005-11-08 00:00:00.000      2005-11-23 00:00:00.000
008      General      20'      DC      Kabul      2000      2005-12-13 00:00:00.000      2006-09-30 00:00:00.000
008      General      40'      DC      Kabul      2450      2005-08-01 00:00:00.000      2005-09-30 00:00:00.000
008      General      40'      DC      Kabul      2675      2005-10-01 00:00:00.000      2005-11-07 00:00:00.000
008      General      40'      DC      Kabul      2755      2005-11-08 00:00:00.000      2005-12-12 00:00:00.000
008      General      40'      DC      Kabul      3155      2005-12-13 00:00:00.000      2006-06-21 00:00:00.000
008      General      40'      DC      Kabul      3255      2006-06-22 00:00:00.000      2006-09-21 00:00:00.000

I want to show to user only the following to Rows:

008      General      20'      DC      Kabul      2000      2005-12-13 00:00:00.000      2006-09-30 00:00:00.000
008      General      40'      DC      Kabul      3255      2006-06-22 00:00:00.000      2006-09-21 00:00:00.000

Becuase the above two rows have max starting date in oder words the above rates is current rate.

Please guide how to show this.

Rgds.
Mehram

Mehram

ASKER

Revised
----------

Sir, I have below query:

select Transno,
CargoType,
CntrSize,
CntrType,
Destination,
amount,
SD=startingDate,
ED=EndingDate
from MTImpAgentRateDet
where transno='008'
group by transno,
cargotype,
CntrSize,
CntrType,
Destination,
amount

Result
--------

008 General 20' DC Kabul 1600 2005-11-24 00:00:00.000 2005-12-12 00:00:00.000
008 General 20' DC Kabul 1900 2005-09-17 00:00:00.000 2005-11-07 00:00:00.000
008 General 20' DC Kabul 1930 2005-11-08 00:00:00.000 2005-11-23 00:00:00.000
008 General 20' DC Kabul 2000 2005-12-13 00:00:00.000 2006-09-30 00:00:00.000
008 General 40' DC Kabul 2450 2005-08-01 00:00:00.000 2005-09-30 00:00:00.000
008 General 40' DC Kabul 2675 2005-10-01 00:00:00.000 2005-11-07 00:00:00.000
008 General 40' DC Kabul 2755 2005-11-08 00:00:00.000 2005-12-12 00:00:00.000
008 General 40' DC Kabul 3155 2005-12-13 00:00:00.000 2006-06-21 00:00:00.000
008 General 40' DC Kabul 3255 2006-06-22 00:00:00.000 2006-09-21 00:00:00.000

I want to show to user only the following to Rows:

008 General 20' DC Kabul 2000 2005-12-13 00:00:00.000 2006-09-30 00:00:00.000
008 General 40' DC Kabul 3255 2006-06-22 00:00:00.000 2006-09-21 00:00:00.000

Becuase the above two rows have max starting date in oder words the above rates is current rate.

Please guide how to show this.

Rgds.
Mehram

Aneesh

Hi Mehram,
try this
select Transno, CargoType, CntrSize, CntrType, Destination,
amount, SD=MAX(startingDate), ED=EndingDate
from MTImpAgentRateDet
where transno='008'
group by transno,
cargotype,
CntrSize,
CntrType,
Destination,
amount

Aneesh

Mehram

ASKER

Sir, when i am trying this its asking for group endingdate and when i am including endingdate column in group by clause it showing all 9 rows instead of 2.
Please help

rockiroads

would this work?

select Transno, CargoType, CntrSize, CntrType, Destination, amount, startingDate, EndingDate
from MTImpAgentRateDet a
where transno='008'
group by transno, cargotype, CntrSize, CntrType, Destination, amount
where transno='008'
where startingdate = (select max(startingdate) from MTImpAgentRateDet where a.Transno = Transno and a.CargoType = CargoType and a.CntrSize = CntrSize and a.CntrType = CntrType and a.Destination = Destination)

Mehram

ASKER

its showing error on where

rockiroads

oopps, I have two wheres!
doh!

change second where to be an and

select Transno, CargoType, CntrSize, CntrType, Destination, amount, startingDate, EndingDate
from MTImpAgentRateDet a
where transno='008'
group by transno, cargotype, CntrSize, CntrType, Destination, amount
where transno='008'
and startingdate = (select max(startingdate) from MTImpAgentRateDet where a.Transno = Transno and a.CargoType = CargoType and a.CntrSize = CntrSize and a.CntrType = CntrType and a.Destination = Destination)

u may need to use IN

select Transno, CargoType, CntrSize, CntrType, Destination, amount, startingDate, EndingDate
from MTImpAgentRateDet a
where transno='008'
group by transno, cargotype, CntrSize, CntrType, Destination, amount
where transno='008'
and startingdate IN (select max(startingdate) from MTImpAgentRateDet where a.Transno = Transno and a.CargoType = CargoType and a.CntrSize = CntrSize and a.CntrType = CntrType and a.Destination = Destination)

Mehram

ASKER

When I am trying this its asking for group statingdate and endingdate

select Transno,
      CargoType,
      CntrSize,
      CntrType,
      Destination,
      amount,
      startingDate,
      EndingDate
from MTImpAgentRateDet a
where transno='008'
and startingdate = (select max(startingdate) from MTImpAgentRateDet where a.Transno = Transno and a.CargoType = CargoType and a.CntrSize = CntrSize and a.CntrType = CntrType and a.Destination = Destination)
group by transno,
      cargotype,
      CntrSize,
      CntrType,
      Destination,
      amount

Mehram

ASKER

Sir, my Boss is calling me I am coming back

rockiroads

remove the group by, u dont need it in the outer
The inner query returns the max date, and we join on that

select Transno,
CargoType,
CntrSize,
CntrType,
Destination,
amount,
startingDate,
EndingDate
from MTImpAgentRateDet a
where transno='008'
and startingdate = (select max(startingdate) from MTImpAgentRateDet where a.Transno = Transno and a.CargoType = CargoType and a.CntrSize = CntrSize and a.CntrType = CntrType and a.Destination = Destination)

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial