聽說這裏有很多sql高手，有個簡單問題請教

最近公司緊縮開支，把另外一個職位的sql工作也交給我幹。可是，我以前很少做這方麵的工作，不太熟悉，有問題請教。謝謝！

Q: 有個table名叫person, has 4 fields, ID, Sales, Date, Rank
For example:
ID Sales Date Rank
10 10000 1/1 0
10 20182 1/2 1
20 10000 1/1 0
20 29765 1/2 1
20 23456 1/3 1

Now, for each unique ID, I want to select the highest rank, if there are two rows with equally highest rank, randomly select one.

The result table looks like
ID Sales Date Rank
10 20182 1/2 1
20 29765 1/2 1

OR
ID Sales Date Rank
10 20182 1/2 1
20 23456 1/3 1

叩謝！

所有跟帖： 

• 麻煩版主高抬貴手，讓這帖流在這一小會兒，謝謝！ -fafa_dd- ♀ (0 bytes) () 03/16/2009 postreply 18:21:42

• 回複：聽說這裏有很多sql高手，有個簡單問題請教 -CaLoanAgent- ♂ (299 bytes) () 03/16/2009 postreply 18:59:34

• Thank you! -fafa_dd- ♀ (0 bytes) () 03/16/2009 postreply 19:24:06

• 我本來是想用self join的，但我對這個不熟，總搞錯 -fafa_dd- ♀ (0 bytes) () 03/16/2009 postreply 19:34:01