Simple MYSQL query help

[Network_ninja]

Hi everyone I have a question on my query.... This is my current records of my table

 

fld1    fld2      fld3          fld4    fld5      fld6

1      lastname1a  firstname1a  mi1a  Address1a  2011-08-26 09:26:44

1 lastname1b firstname1b mi1b Address1b 2011-08-26 09:28:12

1 lastname1c firstname1c mi1c Address1c 2011-08-26 09:28:27

1 lastname1d firstname1d mi1d Address1d 2011-08-26 09:29:18

1 lastname1e firstname1e mi1e Address1e 2011-08-26 09:29:28

 

2 lastname2a firstname2a mi2a Address2a 2011-08-26 09:31:15

2 lastname2b firstname2b mi2a Address2b 2011-08-26 09:31:16

2 lastname2c firstname2c mi2a Address2c 2011-08-26 09:31:17

2 lastname2d firstname2d mi2a Address2d 2011-08-26 09:31:18

2 lastname2e firstname2e mi2a Address2e 2011-08-26 09:31:19

 

3 lastname3a firstname3a mi3a Address3a 2011-08-26 09:34:53

3 lastname3b firstname3b mi3a Address3b 2011-08-26 09:34:54

3 lastname3c firstname3c mi3a Address3c 2011-08-26 09:34:55

3 lastname3d firstname3d mi3a Address3d 2011-08-26 09:34:56

3 lastname3e firstname3e mi3a Address3e 2011-08-26 09:34:57

 

4 lastname4a firstname4a mi4a Address4a 2011-08-26 09:34:53

4 lastname4b firstname4b mi4a Address4b 2011-08-26 09:34:54

4 lastname4c firstname4c mi4a Address4c 2011-08-26 09:34:55

4 lastname4d firstname4d mi4a Address4d 2011-08-26 09:34:56

4 lastname4e firstname4e mi4a Address4e 2011-08-26 09:34:57

 

5 lastname5a firstname5a mi5a Address5a 2011-08-26 09:34:53

5 lastname5b firstname5b mi5a Address5b 2011-08-26 09:34:54

5 lastname5c firstname5c mi5a Address5c 2011-08-26 09:34:55

5 lastname5d firstname5d mi5a Address5d 2011-08-26 09:34:56

5 lastname5e firstname5e mi5a Address5e 2011-08-26 09:34:57

 

I want to extract 1 record per group(fld1) based on the latest date(fld6)

The desired output would be:

 

1 lastname1e firstname1e mi1e Address1e 2011-08-26 09:29:28

2 lastname2e firstname2e mi2a Address2e 2011-08-26 09:31:19

3 lastname3e firstname3e mi3a Address3e 2011-08-26 09:34:57

4 lastname4e firstname4e mi4a Address4e 2011-08-26 09:34:57

5 lastname5e firstname5e mi5a Address5e 2011-08-26 09:34:57

 

 

I thought that this simple query would work:

SELECT * FROM tablename  GROUP BY fld1 ORDER BY fld6 DESC 

 

but this query is not giving me the desired output... tnx in advance

[PFMaBiSmAd]

To get a specific row out of each group of data, see this link - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html