Results 1 to 2 of 2

Thread: mj

  1. #1
    Complicated SQL Problem Guest

    Default mj

    I have a very complicated SQL Problem I am trying to solve, Hopefully some one can help.<BR><BR>Here goes, <BR><BR>I have to select the most current date, ProductID, and interest rate from one table. In this table there are several entries for each ProductID. I am able to get the most current date for each productID just fine by doing a <BR><BR>Select Distinct ProductID, Max(EffectiveDate)<BR>from tblName<BR>Group By ProductID<BR><BR>The Problem comes in when I try to also Select the Interest Rate that corresponds to the Max(EffectiveDate), the query will then return all dates and Interest Rate as I am required to also Group by Interest Rate in the query.<BR><BR>If any one can help, please do, it will be greatly appreciated<BR><BR>thanks,<BR>mj

  2. #2
    Garth Guest

    Default RE: mj

    Try the script listed below.<BR><BR>Garth<BR>www.SQLBook.com<BR><BR><BR>--SQL Script<BR>SET NOCOUNT ON<BR>go<BR>CREATE TABLE tblName<BR>(<BR> ProductID int NOT NULL,<BR> InterestRate decimal(5,2) NOT NULL,<BR> EffectiveDate smalldatetime NOT NULL<BR> PRIMARY KEY (ProductID,EffectiveDate)<BR>) <BR>go<BR>INSERT tblName VALUES (1,7.8,&#039 1/1/00&#039)<BR>INSERT tblName VALUES (1,7.9,&#039 2/1/00&#039)<BR>INSERT tblName VALUES (2,6.8,&#039 2/1/00&#039)<BR>INSERT tblName VALUES (2,7.1,&#039 3/1/00&#039)<BR><BR> <BR>SELECT a.ProductID,<BR> a.InterestRate,<BR> a.EffectiveDate<BR>FROM tblName a<BR>WHERE a.EffectiveDate = (SELECT MAX(b.EffectiveDate)<BR> FROM tblName b<BR> WHERE a.ProductID = b.ProductID)<BR>ORDER BY a.ProductID, a.EffectiveDate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts