0

Hii I wanted to get distinct FireUPRNs but with the newest date (FRADate).

Issue:

I keep getting the error - 

*Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.
Warning: Null value is eliminated by an aggregate or other SET operation.*

Code:

SELECT *

FROM TblFire 
inner join (
SELECT Max(FRADate) as FireDateID, 
FRADate 
FROM TblFire GROUP BY FRADate)
FireDateID
ON FireDateID.FireDateID = TblFire.FireUPRN
Rohit
  • 2,646
  • 6
  • 27
  • 52
indofraiser
  • 1,014
  • 3
  • 18
  • 50

3 Answers3

1

Your query is quite confused, taking the max of a date column and calling it an id. Without a table layout, I think this is what you actually want:

SELECT f.*
FROM TblFire f INNER JOIN
     (SELECT FireUPRN, MAX(FRADate) as FRADate
      FROM TblFire
      GROUP BY FireUPRN
     ) fmax
     ON f.FRADate = fmax.FRADate and f.FireUPRN and fmax.FireUPRN;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I run SELECT DISTINCT FireUPRN FROM TblFire f INNER JOIN (SELECT Max(FRADate) as FRADateID, FRADate FROM TblFire GROUP BY FRADate ) fmax ON f.FireUPRN = fmax.FRADateID I get the same error "Conversion faile when converting datatime from character string) – indofraiser Sep 03 '14 at 10:57
1

Try this: (ID is your primarykey from TblFire)

SELECT * FROM TblFire  t1
JOIN 
(
   SELECT ID, MAX(FRADate) AS FRADate
   FROM TblFire
   WHERE FRADate is not null 
   GROUP BY FRADate
) t2
ON T1.FRADate = t2.FRADate
AND t1.ID = t2.ID
Edi G.
  • 2,432
  • 7
  • 24
  • 33
0

Sometimes it's easier than it looks...

SELECT FireUPRN, Max(FRADate) as FRADate
FROM TblFire 
GROUP BY FireUPRN
indofraiser
  • 1,014
  • 3
  • 18
  • 50