Using SQL to get distinct rows, but also the whole row for those

Ok so its easier to give an example and hopefully some has a solution:

I have table that holds bids:

ID | companyID | userID | contractID | bidAmount | dateAdded

Below is an example set of rows that could be in the table:

ID | companyID | userID | contractID | bidAmount | dateAdded
------------------------------------------------开发者_Go百科--------------
10 | 2         | 1      | 94         | 1.50      | 1309933407
9  | 2         | 1      | 95         | 1.99      | 1309933397
8  | 2         | 1      | 96         | 1.99      | 1309933394
11 | 103       | 1210   | 96         | 1.98      | 1309947237
12 | 2         | 1      | 96         | 1.97      | 1309947252

Ok so what I would like to do is to be able to get all the info (like by using * in a normal select statement) the lowest bid for each unique contractID.

So I would need the following rows:

ID = 10 (for contractID = 94)
ID = 9 (for contractID - 95)
ID = 12 (for contractID = 96)

I want to ignore all the others. I thought about using DISTINCT, but i haven't been able to get it to return all the columns, only the column I'm using for distinct.

Does anyone have any suggestions?

Thanks, Jeff

---

select *
from mytable main
where bidAmount = (
    select min(bidAmount)
    from mytable
    where contractID = main.contractID)

Note that this will return multiple rows if there is more than one record sharing the same minimum bid.

---

Didn't test it but it should be possible with this query although it might not be really fast:

SELECT * FROM bids WHERE ID IN (
  SELECT ID FROM bids GROUP BY contractID ORDER BY MIN(bidAmount) ASC
)

This would be the query for MySQL, maybe you need to adjust it for another db.

---

You could use a subquery to find the lowest rowid per contractid:

select  *
from   YourTable
where  id in 
       (
       select  min(id)
       from    YourTable
       group by
               ContractID
       )

---

The problem is that distinct does not return a specific row - it return distinct values, which ( by definition ) could occur on multiple rows.

Subqueries are your answer, and somewhere in the suggestions above is probably the answer. Your subquery need to return the ids or the rows with the minimum bidvalue. Then you can select * from the rows with those ids.