SQL group by id

If I have a result set like

ID1     ID2
43918   804
43917   804
44047   805
44046   805
44045   805
44044   805
44048   805
45172   806
44298   807
44296   808
44297   808
44294   808
44295   808
44331   809
44330   809
44334   809
44333   809
44332   809
44323   810

How can I开发者_如何学Python limit it to a result set like

ID1     ID2
43918   804
44047   805
45172   806
44298   807
44296   808
44331   809
44323   810

Basically one ID1 for each unique ID2. Columns are from the same table.

select ID1,ID2
from table
where ID2 in (804, 805, 806, 807, 808, 809, 810);

---

select max(id1), id2
from table
group by id2

I'm assuming you want the max. The key point is that you want to do a grouping on the id2 column, and perform some sort of aggregate function on id1 (min, max, sum, avg)

---

 SELECT MIN(ID1), ID2 FROM Table GROUP BY ID2

This gives the result set you used as an example — the lowest ID1 for each ID2. You can use other functions in place of MIN to get other interesting values by ID2 value, the exact list of functions varies by database product.

---

not sure if it is a smart way but you could nest the queries something like this

select 
    t1.ID2, 
    (select TOP 1 id1 from table where id2 = t1.id2 from table as t2) as ID1 
from table as t1 
where ID2 in (804,805,806,807,808,809,810);

TOP 1 is SQL Server specific, if you are on mysql/postgres you'd need to select ... LIMIT 1

---

Using group by and then an aggregate function on ID1 column

SELECT MAX(ID1) AS ID1, ID2
FROM TABLE
GROUP BY ID2

This would give you the highest ID1 value associated with ID2