6

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!

1
  • 1
    The row limit is now 50000
    – rene
    Feb 4, 2015 at 8:27

3 Answers 3

2

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

1
  • 1
    The row limit is now 50000
    – rene
    Feb 4, 2015 at 8:26
2

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
    
2
  • 1
    Lol, #3 is funny. I would of never thought of doing something like that
    – TheLQ
    Aug 27, 2010 at 22:13
  • 1
    The row limit is now 50000
    – rene
    Feb 4, 2015 at 8:25
0

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

1
  • 1
    The row limit is now 50000
    – rene
    Feb 4, 2015 at 8:25

You must log in to answer this question.

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