Select Distinct for more than 1 distinct value

[Mark Li]

I have a database setup with id and 'dogname.' I would like to retrieve distinct values of dogname, but allow for 2 of each dogname. So instead of limiting it to one distinct value, there could be 2 identical dognames that are retrieved, but no more than that.

Is there a way to set this kind of limit for distinct?

[villas]

I can't see how distinct would help there because it only returns unique rows.

I would say that the main question is how much data you have.  I mean,  it is probably easier to grab all the data and then discard (or simply loop over) the data you do not need.  However,  if you have a huge number of records that may not be practical.

[Mark Li]

I'll be looping over them for now, is there anyway to use a query or select() to return a set with no more than 2 of each name?

[villas]

I have only seen complex queries to solve this.  The sql can differ according to the DB.  

Example for SQL Server:

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=SelectTopNByGroup 

Sometimes grabbing more data than you need and then discarding it is quicker and easier than making queries with complex joins etc.  I would also be interested if anyone suggests anything though.

[Derek]

Usually, when you are asking these kind of questions, you come to realize you are asking the wrong question.