Throwout Dates 1

[Jason777]

Hello all,
I have a query that is pretty basic, it returns a result set like this

SLSMANID, CUST ID, CONTRACT#, DATE PAID
001 3344 101 5/14/03
001 3344 101 5/15/03
001 4455 115 5/25/03


Basicly I need one line per contract #, throwing out everything but the most recent date. The date field comes from a read only table. I am using Dharma 8.00 so I'm posting in the Ansi forum. Thanks -J

 

[JamesLean]

Try this:

SELECT * FROM mytable t1
WHERE date_paid = (
  SELECT MAX(date_paid) FROM mytable
  WHERE contract = t1.contract
)

Not sure it will be valid for your DBMS though.

--James

 

[Jason777]

just slightly confused about the mytable and the t1. The table I am using is called "commpaid" would I feed that into "mytable" or "t1" and what would I do for the other table?

 

[Jason777]

This is what I have now

SELECT *
FROM commresalepaid
WHERE date_paid = (
SELECT MAX(date_paid) FROM commresalepaid
WHERE contract_number = contract_number
)

The problem is this only returns 1 result, the 1 record with the highest date.

 

[JamesLean]

t1 is the alias to enable the subquery to reference the table. So your query would be:

SELECT *
FROM commresalepaid t1
WHERE date_paid = (
  SELECT MAX(date_paid) FROM commresalepaid
  WHERE  contract_number = t1.contract_number
)

--James

 

[Jason777]

James - It worked like a champ - Thanks buddy.

I'm just a hardware monkey that got thrown into this SQL deal...

-J