SQL Aggregate function issues

Question

I have a table that has the following data:

id  status  date    part_no part_name 

1   high    1/2/09  55      screw;
1   medium  1/2/09  55      screw; 
2   high    2/2/09  32      plug;
3   low     4/8/09  59      bolt;
4   medium  5/6/09  48      tie;
4   low     5/6/09  48      tie;

I want to write a query that will give me one row per id number and I want to be able to show all the fields in the result. for instance the result of my query should be:

1   medium  1/2/09  55  screw;
2   high    2/2/09  32  plug;
3   low     4/8/09  59  bolt;
4   low     5/6/09  48  tie;

Thanks in advance for any help you can give.

Answer 1

SELECT
    id,
    MIN(status),
    MIN(date_part),
    part_name
FROM <table>
GROUP BY
    id,
    part_name

Answer 2

   SELECT id, 
          MIN(status), 
          date, 
          part_no, 
          part_name  
    FROM table
GROUP BY id, date, part_no, part_name

..is what you're going for, but the complication is that you're storing strings so it depends on the db for how it applies the MIN function to text/characters. In a normalized setup, the status would be a separate code table, and the key would/could be an ID which would work better with the MIN function.