It only returns 50,000 rows when I do the query below. I thought there are more than that.
select id,DisplayName from Users
Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.
Sign up to join this communityIt only returns 50,000 rows when I do the query below. I thought there are more than that.
select id,DisplayName from Users
As already said in the comments: we limit the result set to a maximum of 50,000 rows
To get all rows without the artificial limits opposed by the Data Explorer your best bet is to get the datadump as pointed out by @ben is uǝq backwards, import all data in your own database (like mysql or sqlserver) and run your analysis there. To give you an indication, these are the current row counts for some of the major tables on Stack Overflow:
table count
----------- ----------
users 3,227,916
posts 20,580,052
posthistory 49,728,723
votes 62,972,310
comments 30,468,154
For a very limited use case and when you only want to retrieve less then an handfull of columns you could use a couple of CTE's and join the results to give you what I call a wall of users. Technically this returns more than 50,000 id and diplayname pairs from the Users table (it returns 250,000 pairs). I leave it as an excercise for the reader to try to add more columns but please don't try to timeout the server.
With the newer versions of SQL Server, we get more options to shape the output data. The FOR JSON AUTO clause is beneficial in this context. Using that statement in a query like this one
select top 250000 *
from users
for json auto
returns you all the data in json in just under 40,000 rows. So there is still room to fetch more then 250,000 users specially if you reduce the columns to include.
And you concluded your self, select * from users order by reputation desc
is a reasonable way to limit and/or give meaning to so many rows.
Since December 2016, you can also access Stack Exchange data via Google’s BigQuery.
This has the advantages of:
The BigQuery data is updated at about the same rate as the Data Dump (which should be fine for such large amounts of data).
Unfortunately, BigQuery seems to still only contain Stack Overflow data, and not for other sites, but I haven't rigorously tested this in a long while...
If the total number of rows is not orders of magnitude larger than 50000, you can just repeat the query a few times, download the results of each query, and merge them in a spreadsheet, etc. For example:
select top 50000 Id, DisplayName from users
where Id>##MinId##
order by Id asc
Initially MinId = 0, but for subsequent runs it should be the Id in the last row returned by the previous run.
Ideally, this is not something you would do often because it puts a bit of strain on the server.