CODEWITHSUNDEEP
sqlsql-server-2005
user380432
user380432

Reputation: 4779

SQL Group by MAX(Date)

I have the following query. There are many inspectiondates for each driver license number. So the records can be as follows:

InspectionDate         DriverLicenseNumber
2010-01-02 00:00:00         123
2010-01-05 00:00:00         123 
2011-01-05 00:00:00         345 


  SELECT 
MAX(InspectionDate),
DriverLicenseNumber
FROM #Test
 GROUP BY DriverLicenseNumber

Why do I still get multiple multiple dates and for the same driver license number?

Thanks

Upvotes: 1

Views: 3327

Answers (3)

montelof
montelof

Reputation: 501

just change DriverLicenseNumber to integer in your table and then your query will work as it is.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

Try this and it should test Damien's thought...

SELECT
  MAX(InspectionDate),
  CAST(DriverLicenseNumber AS INT)
FROM
  #Test
GROUP BY
  CAST(DriverLicenseNumber AS INT)


Or possibly this...

SELECT
  MAX(InspectionDate),
  LTRIM(RTRIM(UPPER(DriverLicenseNumber)))
FROM
  #Test
GROUP BY
  LTRIM(RTRIM(UPPER(DriverLicenseNumber)))

Upvotes: 3

Anish Patel
Anish Patel

Reputation: 767

The following code illustrates that this is not actually an issue, like Martin said above, there must be something else going on.

CREATE TABLE #test
(
    a DATETIME,
    id INT
)

INSERT INTO #test VALUES ('20100102', 123)
INSERT INTO #test VALUES ('20100105', 123)
INSERT INTO #test VALUES ('20110105', 345)

SELECT MAX(a), id 
FROM #test
GROUP BY id

DROP TABLE #test

Upvotes: 3

Related Questions