SQL: select by count & value

hi experts, I've a question in doing a select. It could be simple query but I am clueless as to how to do it with a select stmt. I've a following table called 'states' with the following two columns (state_id, state) with the following values:

state_id state
-------- -------

 1        illinois
 2        california
 3        illinois
 4        montana
 5        california
 6        california
 7        georgia
 8        georgia
 9        illinois
 10       illinois

I would like to retrieve 7 state_ids with first 4 illinois state_ids, and next
3 california ids. That is, I want the result to be like this (in the same order as stated below):

state_id state
-------- -------

  1         illinois
  3         illinois
  9         illinois
  10        illinois
  2         california
  5         california
  6         california

Your help is much appreciated. Thanks Received on Fri Feb 28 2003 - 05:40:36 CET