What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 133 Stack Exchange communities.

I want to get all the Tags from the data explorer (http://odata.stackexchange.com/meta/query/new) but my query results get truncated to 2000 records. How can I get the whole thing?

Thanks!

share|improve this question

3 Answers 3

You can't make it return more than 2000 rows, it's a hard limit to stop queries from overloading the system. You can do piecemeal queries like:

SELECT * FROM Tags WHERE Id < 2000;
SELECT * FROM Tags WHERE Id >= 2000 AND Id < 4000;​
-- etc.

but that would take a while (we're on id 29239 right now). It's probably easier to download the latest data dump yourself so you can load up the whole database and operate on it locally

share|improve this answer
    
ok, thanks, will try that –  user150486 Aug 27 '10 at 22:20

There are almost 30,000 tags in the Stack Overflow data set, but the Data Explorer is limited to returning 2,000 results at a time.

EDIT: Here is the best solution. Output the results of this to text so you can copy/paste. This generates the entire set of tags as an XML document, which you can parse:

    SELECT TagName AS Name FROM Tags FOR XML RAW('Tag'), ROOT('Tags')

Since you're free to use any SQL you want, there are a bunch of ways of doing this, not all of which work.

  1. Write a query to return a specific 2,000 records in each query, and run it 15 times (basis of a query is shown in Michael's answer)

  2. Write a query that concatenates the data you want into a string, run that, and process the results yourself. This query times out for some reason. (I used a WITH because I was testing TOP n.)

    DECLARE @results nvarchar(MAX)
    WITH c AS
    (
        SELECT TagName FROM Tags
    )
    SELECT @results = COALESCE(@results + ',', N'') + TagName FROM c
    SELECT @results
    
  3. Get tricky (this actually works, though the tags are not in alpha order -- I assume you can take care of that later).

    SELECT
        t1.TagName, t2.TagName, t3.TagName, t4.TagName, t5.TagName,
        t6.TagName, t7.TagName, t8.TagName, t9.TagName, t10.TagName,
        t11.TagName, t12.TagName, t13.TagName, t14.TagName, t15.TagName
        FROM
            Tags t1
            LEFT OUTER JOIN Tags t2 ON t2.Id = t1.Id + 2000
            LEFT OUTER JOIN Tags t3 ON t3.Id = t2.Id + 2000
            LEFT OUTER JOIN Tags t4 ON t4.Id = t3.Id + 2000
            LEFT OUTER JOIN Tags t5 ON t5.Id = t4.Id + 2000
            LEFT OUTER JOIN Tags t6 ON t6.Id = t5.Id + 2000
            LEFT OUTER JOIN Tags t7 ON t7.Id = t6.Id + 2000
            LEFT OUTER JOIN Tags t8 ON t8.Id = t7.Id + 2000
            LEFT OUTER JOIN Tags t9 ON t9.Id = t8.Id + 2000
            LEFT OUTER JOIN Tags t10 ON t10.Id = t9.Id + 2000
            LEFT OUTER JOIN Tags t11 ON t11.Id = t10.Id + 2000
            LEFT OUTER JOIN Tags t12 ON t12.Id = t11.Id + 2000
            LEFT OUTER JOIN Tags t13 ON t13.Id = t12.Id + 2000
            LEFT OUTER JOIN Tags t14 ON t14.Id = t13.Id + 2000
            LEFT OUTER JOIN Tags t15 ON t15.Id = t14.Id + 2000
        WHERE t1.Id <= 2000
    
share|improve this answer
1  
Lol, #3 is funny. I would of never thought of doing something like that –  TheLQ Aug 27 '10 at 22:13
    
insane! thanks! –  user150486 Aug 27 '10 at 22:35

Its truncated to 2000 rows due to performance and to prevent possible abuse. To get everything requires using the full data dump in XML or using another database port

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .