SQL Help please

I have a table like this:
Name Date Pay
----------------------------
Bob 1/2/2002 $4
Bob 3/14/2002 $6
Bob 5/2/2002 $8

I need to grab each distinct username and their latest pay amount without showing all past pay amounts too. I've tried "SELECT Name, MAX(Date), Pay FROM Pay GROUP BY Name,Pay" But it basically just gives me multiples of the same name with all dates and pay amounts for that name. Any help would be greatly appreciated.

Here's a statement that will work with MSSQL. If you aren't running MSSQL, let me know what SQL Server you are running...some support sub-selects, some don't...

select Name,Date,Pay from Pay WHERE Name+convert(varchar(10),Date) = (select Name+convert(varchar(10),MAX(Date)) from Pay GROUP BY Name)

Sometimes the order of fields in a select statement can be your downfall.

The date should be your last field in the Select statment. Try the following out:

SELECT Name, Pay, MAX(Date)
FROM Pay
Group by Name, Pay

You stated that you tried:

SELECT Name, MAX(Date), Pay
FROM Pay
Group by Name, Pay

The two SELECT statements provides two different results. This is due to the placement of the MAX function.

Hi,

I hope that the below query will be very useful for you, Please check it..

Select b.Name , b.Date , a.Pay
from table a inner join
(select name ,max(date) as Date from table
group by name) b on a.date = b.date and a.name = b.name

Thanks & Regards
SakthiMeenakshi.S
-----------------------------------------------------------