Edit: Just wanted to add the below is my opinion and I'm learning as well so wanted to put my thoughts out there for comment.

Distinct works to return unique records across all of your return values and so it will return all values in your case because record 55|53 is different than record 55|56.

Link below with the same answer in case I stated something incorrectly.

https://stackoverflow.com/questions/...rns-duplicates.

It would seem to me that the pairing part should be done in an application logic outside of SQL. I would just return IDs where wins are equal to your program and then have it do the pairing.

That being said I'm fairly certain most DBMS systems come with regular programming facilities that you could use if you wanted to keep it all within the same procedure but I don't know if using database manipulation techniques is what you want to do.

So you want all the cases where 2 players have the same number of wins?

What's wrong with your current query (and I don't think the distinct is necessary)?

Oh, I see what you mean. Hmm. How about

This rather arbitrarily picks the lowest playerid for a player with the same number of wins.

Thanks guys.

Rusty, your suggestion definitely gets me closer, but still not quite what I'm looking for:

id1 | id2
-----+-----
56 | 49
54 | 49
53 | 51
52 | 51
55 | 51
49 | 50
50 | 49
51 | 52

id2 column should only contain unique entries, as id1 column does.

I don't think there's really a way to do that with sql. For one thing, it might not be possible to even make such matchups. Consider what happens if there are only 3 players and all 3 have the same number of wins. A plays B and C plays who?

I'd do it outside of SQL personally. I'd get a list of all the matchups like you had in your original query (minus the distinct) and then picks matchups, eliminating rows that were no longer possible.

Do you want very strictly players with equal wins, or do you want to pair all players with the most appropriate opponent? For instance, if there are three players with 5 wins, should one of them be paired with someone with 6 wins or 4 wins?

Also, what version of SQL are you using? My solution would involve using SQL Server's OVER (PARTITION BY) clause, which I think works differently in other SQL versions.

I do agree with Rusty that SQL isn't really the most appropriate platform to do this on, because it's not really a set-based operation. It can probably be done, though.

I wouldn't say it's impossible but I'd say it's probably pretty torturous

That helps. I'm doing this project through Python's DB-API so we aren't necessarily limited to only SQL statements. I'll see what I can do with Python to get the desired records.

ChrisV, I'm looking for pairings with the most appropriate opponent. As for which version of SQL I'm using--I'm not sure. The course has us using postgresql through a virtual machine.

SELECT a.id as id1, b.id as id2
FROM (SELECT ROW_NUMBER() OVER (ORDER BY won DESC, id) rnum, * FROM playerstandings) a
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY won DESC, id) rnum, * FROM playerstandings) b ON b.rnum = a.rnum + 1
WHERE a.rnum % 2 = 1

I think that should work, and I think that syntax is right in postgres. Not completely sure on both counts

Just to explain briefly: ROW_NUMBER() OVER gives each row a numbering based on the ordering in the brackets, I've then aliased the resulting row number to "rnum". So it joins the players by row numbers that are one apart, 1v2, 2v3, 3v4, 4v5 etc. Then the WHERE clause makes a's rnum be odd, so we end up with 1v2, 3v4, 5v6, etc.

If I understand that right, it's going to sometimes pair a guy with a person who has less wins than him, in cases where there aren't even numbers of wins. It's a little bit like if you ordered all the players by # of wins and then said, OK, #1 plays #2, #3 plays #4, #4 plays #5 and so on. Is that right?

It's kinda neat to see that implemented in SQL, but, this would be like the simplest SQL query ever, munged into shape by like 1 simple line of python. I would probably never use a query like this, unless I was using some kind of 3rd party system where my only inputs were SQL queries (I have dealt with stuff like that before, like places where you do mail merges by supplying a SQL query and no other option)

That's what he's asking. Postgresql after something like (not certain) 9.0+ supports "windowing" (OVER x [PARTITION BY y])

Also without thining about it too much, prob agree with Rusty and this would be a problem for "SQL to pull the player/winrate data, application logic to sort it out" for me usually.

It's exactly that.

Agreed. The query as it stands isn't horrible, but then the next thing is going to be "hey I notice that when there's an odd number of players, it just leaves the last one off, can we fix that?" and so forth. And before you know it you'll end up with an abomination, because SQL was designed to manipulate sets, not ordered lists.

btw, normally I would alias the repeated subquery:

WITH orderedplayers AS (
SELECT ROW_NUMBER() OVER (ORDER BY won DESC, id) rnum, * FROM playerstandings
)
SELECT a.id as id1, b.id as id2
FROM orderedplayers a
JOIN orderedplayers b ON b.rnum = a.rnum + 1
WHERE a.rnum % 2 = 1

but I have a hunch WITH might be a SQL Server thing and was too lazy to look up whether it works in postgres.

Pretty sure postgresql does WITH, don't think I've ever used it tho so not sure if same as MSSQL

I've actually frequently encountered performance problems when using WITH instead of a CTE, but I use it anyway for non performance critical queries because it's easy.

WITH in PostgreSQL is a CTE. IRT optimization fences, that's a known problem, but the Postgres team also refuses to add query hinting.

For anyone who hasn't tried out windowing, it's so amazing once you get used to it. It's really hard to use MySQL knowing it doesn't exist, to be honest.

Of course, the syntax in Postgres is different than what ChrisV shows here. There is a way to simply grab the max or min of each window in the partition over section, using (I think) the DISTINCT ON clause.

And finally, you probably shouldn't do views on views, or rather, don't do functional queries over views, like windowing, aggregations, limit 1 clauses, etc. It's a lot of work for the database and often confuses the query planner. I would just write an entirely new view for what Mossberg is attempting.

is this actually a random shuffle? I remember there being some gotchas with shuffling.

it looks like a correct implementation of fisher-yates:

https://bost.ocks.org/mike/shuffle/

You can use the STL's shuffle since C++11

http://en.cppreference.com/w/cpp/alg...random_shuffle

Also, next_permutation and its sister prev_permutation are interesting if you want to exhaust a deck of cards.

random_shuffle is deprecated. Boost has options as well.

I am learning C++ and ran into an issue with Taylor Series Approximation of e^x to n terms. Here is code for the relevant functions:

Taylor.cpp
factorial.cpp

The Taylor function starts diverging from the correct answer as x and n increase. I believe the problem is in the floating point division. Do I need different types? I think I can at least prevent the function from returning nonsensical with some error handling:

This is probably just your factorial overflowing. This would happen at n=21 (64bit) or n=13 (32bit), not sure what integer width is used by default in your factorial method. (Your factorial is returning uint64, but using a regular int during calculation.)

Bingo! This was the problem. Forgot to switch factorial to uint64 I can at least get accuracy for bigger n now. Thanks.

There are also infinite precision integer and float libraries out there, if you don't care about performance.

I've never written programs that run as their own thing on windows, and I'm curious how to implement this:

It's from a thread on Reddit about programs for moving the mouse slightly every x minutes.

Just curious what implementation would be like, what library I'd have to use, also if I have bash for windows if I could write and compile it there without installing visual studio