I have a table with the following data

 

column1 column2

a 1

a 2

a 2

b 2

b 1

b 3

 

What I want is the top 10 columns with the same column 2. So something like

 

1 a

1 b

 

and so on for each column 2 data. Could someone please tell me what the query would be. Thanks

I have attached a text file with sample data. You can see there could be several companies that might have been founded in the same year. I want to retieve only 10 companies for each year. Could someone please help me with this query?

Sample.txt

Can you please learn to use the same thread when you want to add to your question rather than making a new thread...as this is just getting confusing having a lot of topics for this one question.

 

Thank you.

I have attached a text file with sample data. You can see there could be several companies that might have been founded in the same year. I want to retieve only 10 companies for each year. Could someone please help me with this query?

 

Try this:-

 

http://www.petefreitag.com/item/59.cfm

Top 10 will get me only top 10 recors, I want top 10 records for each year...

Not quite sure what you mean.

Try this

SELECT * FROM tablename WHERE column2 = 'value' LIMIT 10;

Ask again if that isn't what you wanted

I don't wan to limit the table by any criteria. I want a max repeat of the year to be 10 and the associated company id's. Let me know if this is not clear, you could opne the attachment, that would make things clearer

Sorry, maybe I'm being a bit thick but I'm still not sure what you want to do.

You say you don't want to limit the results but you also say that you want a 'max repeat' of the year to be 10 which by definition is a limitation.

If you could give me another example, that might help me help you.

Please see the attachments..raw is a sample of the data I have..finalresults is what I want..

 

A max repeat of 10 id's for the same yearfounded

raw.txt

finalresult.txt

You could use UNION in your query to join the result sets together like this:

SELECT id, yearfounded FROM tablename WHERE yearfounded = 1987 LIMIT 10 ORDER BY id 
UNION SELECT id, yearfounded FROM tablename WHERE yearfounded = 1999 LIMIT 10 ORDER BY id;

soory for being an ass but wouldn't that limit my results to the the two years that are mentioned. I want a group of 10 companies for evey year founded in the list.

I don't think that this can be done in a single SQL statement.

You'll have to use a loop within a procedure, methinks:-

 

1. Get an array of years with a SELECT DISTINCT.....

2. Loop through the array with a SELECT TOP 10 FROM ..... WHERE Year=:currentIndex, type of command.

3. Add the results from each loop into another array/cursor and return that.

 

I'll see if there is another way using pure SQL when I get a few minutes.....