|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
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. |
|
#2
|
|||
|
|||
|
RE: mysql?
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) |
|
#3
|
|||
|
|||
|
RE: SQL Help please
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. |
|
#4
|
|||
|
|||
|
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 ----------------------------------------------------------- Quote:
|
![]() |
| Viewing: Codewalkers Forums > Other Technologies > Database Help > SQL Help please |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|