Select Distinct

[erbrose]

Hey, I keep searching the forum, but can't exactly what I need.
I have a table (tbl_road) with tons of fields. I need to create a new table (tbl_road_distinct) with unique values of an field (road_id), as there are multiple values,. So, this works fine

CREATE TABLE tbl_road_distinct
SELECT DISTINCT (road_id)
FROM tbl_road

thats fine, but i want it to bring the rest of the attributes associated with the unique records over too (ideally the first occurrence of each record). I Do not have a PK either as its just a temp table..
Any thoughts would be greatly appreciated
Eric
PS I am working with MySQL 5.*
MySQL Query Browser 1.*
on Windows XP

[erbrose]

Hey, I keep searching the forum, but can't exactly what I need.
I have a table (tbl_road) with tons of fields. I need to create a new table (tbl_road_distinct) with unique values of an field (road_id), as there are multiple values,. So, this works fine

CREATE TABLE tbl_road_distinct
SELECT DISTINCT (road_id)
FROM tbl_road

thats fine, but i want it to bring the rest of the attributes associated with the unique records over too (ideally the first occurrence of each record). I Do not have a PK either as its just a temp table..
Any thoughts would be greatly appreciated
Eric
PS I am working with MySQL 5.*
MySQL Query Browser 1.*
on Windows XP

So it looks like this does what I want.... am I correct in my assumption???

CREATE TABLE tbl_road_distinct
SELECT * 
FROM tbl_road
GROUP BY road_id