6

It only returns 50,000 rows when I do the query below. I thought there are more than that.

select id,DisplayName from Users 
  • 1
    There's a limit on the number of rows you can return I think... Tim Stone will know for sure. – ben is uǝq backwards Dec 21 '13 at 10:59
  • How do I get all rows then? Must be a way out there bud. – Ali Gajani Dec 21 '13 at 11:00
  • 2
    Download the data dump? Why do you need that information at all? Can't you make your query more specific? – ben is uǝq backwards Dec 21 '13 at 11:01
  • 4
    Really?!?! You want to print out 3 million users on a single webpage? My browser already lags on 50,000... – Mysticial Dec 21 '13 at 11:01
  • 6
    You are quite right - we limit the result set to a maximum of 50,000 rows. – Oded Dec 21 '13 at 11:04
  • Decided to return top 50k users. :) – Ali Gajani Dec 21 '13 at 11:09
8

Since December 2016, you can also access Stack Exchange data via Google’s BigQuery.

This has the advantages of:

  1. Not limited to 50,000 rows, like SEDE is.
  2. Has a REST API.
  3. Don't need to download the huge dump data and run your own SQL server.
  4. It's easier to do fun things by mashing up with other data sets on BigQuery. For example you could cross query the Stack Overflow data with the Medicare data, to try and figure out what meds user-x was on when he wrote that question...

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...

  • 1
    SEDE is simple to use. You write a query, you get results. To use this BigQuery you have to: 1) Have Google account. 2) Create Project. 3) Dunno,I gave up. So sorry, this is not a good alternative to SEDE at all. – Shadow Wizard is Ear For You Apr 9 '18 at 19:03
  • 1
    @ShadowWizard, you already have a Google account or you've not worked in tech long. Sorry you're allergic to mild effort, but using BigQuery is not that much of one. BigQuery is an excellent option to have, given the limitations of both SEDE and the plain Data Dump. – Awesome Poodles Apr 9 '18 at 19:13
  • I spent 6 hours today compiling old C++ project at work, this required looking for old Visual Studio in dusted cabinets in office, copying to collegue computer then to USB stick since DVD reader on my computer didn't work, installing the old Studio, figuring out why it can't find external links in the project, and fixing some weird bugs on the way, which didn't cause problems in the past (while not really familiar with C++, it wasn't my code). So no, I'm not allergic to mild effort, I prefer to not spend hours when I can spend minutes to get same result. Even if it's bit limited. – Shadow Wizard is Ear For You Apr 9 '18 at 19:51
6

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.

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.

3

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.

You must log in to answer this question.

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