|
|
# ? Oct 15, 2017 18:34 |
|
|
# ? Jun 4, 2024 16:08 |
|
Nolgthorn posted:It feels like the gender and the reciprocal relationship are separate things. The gender is just one attribute of an unlimited number of possible attributes which could be assigned to a character. The reason for the gender in the reciprocal relationship table is so that i can do things like: <Aunt>,<Female>,<Niece> <Aunt>,<Male>,<Nephew> The Common set of attributes are actually columns in the Character table, the Custom Property table is for other properties. The reason I was trying to use IDs and Foreign Keys in all of this was for Referential Integrity, but if I abandon that and just use the description fields instead of IDs I think my problems go away or are at least minimized. I can provide a "clean-up" function where descriptions that are no longer defined in the appropriate tables can be removed from characters (or retained, doesn't really matter at that point). Thanks for the response, it gives me some options and things to think about.
|
# ? Oct 15, 2017 18:55 |
|
The six permanently unread posts on this thread reminds me: ZDR since you've posted here in the past can you share what went wrong with your query?
|
# ? Oct 15, 2017 19:52 |
|
I had to post my response (first comment on this page) two times, the first time it said my post was successful and then it said the page it was trying to send me to didn't exist. If I backed out of the thread I was the most recent poster. But the page didn't exist. I had to post again. That got my post to appear and the page to exist.
|
# ? Oct 16, 2017 01:23 |
|
Nolgthorn posted:I had to post my response (first comment on this page) two times, the first time it said my post was successful and then it said the page it was trying to send me to didn't exist. If I backed out of the thread I was the most recent poster. But the page didn't exist.
|
# ? Oct 16, 2017 02:20 |
|
This is so meta
|
# ? Oct 16, 2017 03:02 |
|
I bet it was because of me posting in between ZDR so now if I post I'll fix it.
|
# ? Oct 16, 2017 22:00 |
|
Methinks not. Just now it said six new, yet there were none. Just get some baby goon to sign an NDA, give em a code dump, and let them track down the issue.
|
# ? Oct 18, 2017 00:09 |
|
I'm pretty sure the problem is that the "number of posts in the thread" is materialized in the thread record, and this is incremented every time someone posts. Meanwhile, the unread post count probably looks directly at the post records, so now that 6 posts have been deleted from the post table, the number of read posts is always at least 6 less than the "number of posts" on the thread. So either zdr can fix all threads where the post count doesn't line up, or we can just deal with it and/or recreate the thread.
|
# ? Oct 18, 2017 18:01 |
|
With MySQL, is it possible to group by just certain groups and have everything else grouped as "Other"? Example: I just want group 1 and 2 on their own and everything else grouped together. Table: group | quantity -------------------- group1 | 5 group1 | 2 group3 | 4 group2 | 1 group1 | 6 group3 | 8 group4 | 5 group5 | 1 I want a result set like this where I sum the quantities: group1 | 13 group2 | 1 other | 18 Possible with one query?
|
# ? Oct 23, 2017 19:24 |
|
kiwid posted:With MySQL, is it possible to group by just certain groups and have everything else grouped as "Other"?
|
# ? Oct 23, 2017 19:47 |
|
Kuule hain nussivan posted:You could use a case in your initial select to lump the additional groups into other and then group by those values. Not sure if you can do it in one query, but it'll definitely work with a subquery. It can probably be done directly in line. code:
|
# ? Oct 23, 2017 20:00 |
|
McGlockenshire posted:Oh also we should start a new thread because according to all the reports in QCS none of the broken threads are getting repaired, and new posts in this thread will be unreadable once it adds a new page. Sorry, all the replies to your question are on the unreadable new page.
|
# ? Oct 23, 2017 20:44 |
|
McGlockenshire posted:It can probably be done directly in line. Yeah, I wasn't sure if MySQL would allow the alias for the groups to be used in the group by statement of the same query that creates the alias, but that's cool and good. Edit: Thread seems to have fixed itself!
|
# ? Oct 23, 2017 21:14 |
|
Kuule hain nussivan posted:Edit: Thread seems to have fixed itself!
|
# ? Oct 23, 2017 21:38 |
|
anthonypants posted:Please do not troll the SQL thread.
|
# ? Oct 24, 2017 07:56 |
|
Kuule hain nussivan posted:It looked fine while I was the most recent poster Testing this theory. It shows six unread for me right now, none unread within the normal thread view, but the last six posts (which I've already seen a few dozen times) are showing as unread on the "post reply" page. edit: Nope, still the same behavior.
|
# ? Oct 24, 2017 14:34 |
|
wolrah posted:Testing this theory. It shows six unread for me right now, none unread within the normal thread view, but the last six posts (which I've already seen a few dozen times) are showing as unread on the "post reply" page. It seemed to be a feature of the Awful app. When I post, it marks the thread as having no unread posts, but this changes back to 6 unread as soon as I refresh my bookmarks-/subforum-view. In short, still hosed.
|
# ? Oct 24, 2017 14:44 |
|
Kuule hain nussivan posted:It seemed to be a feature of the Awful app. When I post, it marks the thread as having no unread posts, but this changes back to 6 unread as soon as I refresh my bookmarks-/subforum-view. In short, still hosed. Makes sense. I think the weird behavior I see on the post page is an artifact of the SALR Chrome extension and not actually something the forums are directly doing (though it's still obviously getting triggered by the forum bug).
|
# ? Oct 24, 2017 15:10 |
|
We've got some reports running off an Oracle 10g(?) database on an RHEL 5 server, and the reporting server is running SQL Server 2005 on Server 2003, and I'm looking at the MSSQL Migration Assistant, and it looks really nice. Except for the estimated 60-day conversion time, that's not so great.
|
# ? Oct 24, 2017 19:58 |
|
T-SQL question: We have an application that creates stored procedures that our customers write to save for later on our reporting DB. Something I noticed tonight when investigating problems with this system that has not been touched in yonks is a delay between the CREATE PROCEDURE command being issued and the sproc actually showing up as a SQL object. I clocked this delay in one case as being between 10 and 12 minutes. Interestingly, when the SQL object showed up in sys.procedures, the create_date and modified_date showed a timestamp of right around the time the CREATE PROCEDURE command was issued. Has anyone seen this behavior before?
|
# ? Oct 26, 2017 02:10 |
|
Nth Doctor posted:T-SQL question: Mystery solved: it's a red herring. My role on the server wasn't being granted any permissions on the new sproc at creation time, so I couldn't even know it existed. A job came around and granted me the permissions a while later.
|
# ? Oct 26, 2017 03:33 |
|
I'd like to keep a record of the first time a string occurred, plus the number of times it occurred.code:
|
# ? Oct 26, 2017 12:31 |
|
This is actually built into Mysql almost exactly what I needed. https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html code:
|
# ? Oct 26, 2017 12:50 |
|
Nolgthorn posted:This is actually built into Mysql almost exactly what I needed. Looks like postgress uses ON CONFLICT https://www.postgresql.org/docs/9.5/static/sql-insert.html
|
# ? Oct 26, 2017 14:02 |
|
Is there a good resource for "Here's a list of PostgreSQL extensions that are actually solid and useful and which you could consider using in production"?
|
# ? Oct 26, 2017 16:16 |
|
Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table. WITH PartList AS ( SELECT * FROM dbo.Part WHERE PartId = '42' UNION ALL SELECT a.* FROM dbo.Part a INNER JOIN PartList p ON a.ParentPartId = p.PartId ) SELECT * FROM PartList
|
# ? Oct 27, 2017 01:33 |
|
Withnail posted:Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table. It looks like you want a full hierarchy tree starting with the root at PartId 42, correct? Recursive CTE like you have is what you'll want but you should add a level to the query like this: code:
|
# ? Oct 27, 2017 01:49 |
|
I want it expanded like below. Your example doesn't seem to be doing this. I'm used to using oracle's 'connect by' to do this...
|
# ? Oct 27, 2017 02:02 |
|
Withnail posted:Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table. code:
|
# ? Oct 27, 2017 15:11 |
|
Jethro posted:
Cool approach. I like it.
|
# ? Oct 27, 2017 15:22 |
|
Woah did this thread just fix itself?
|
# ? Oct 27, 2017 19:19 |
|
What's a good resource for transitioning out of the "I can write queries and join tables" phase. I know that procedures and parameters and whatnot are a thing, but I always fall back to just doing all my data manipulation in python. I'd like to be able to take more advantage of the DB engine, but outside of "a table is a thing with columns where you put stuff" I've never really had an opportunity to learn how they should be used.
|
# ? Oct 27, 2017 19:25 |
|
Trying to figure out how to work out how get a combination of rows with MySQL. I actually have no idea where to start with this. My goal is to get a count of how many times a game (the GameID) was the same for each/any combination of PlayerIDs, so I can figure out how many total times a combination of players has occured. I need to do this with every single combination of players (if possible). PlayerLog table: code:
Player1ID(55) + Player2ID(72) = 2 games Player2ID(72) + Player3ID(82) = 2 games Player1ID(55) + Player4ID(90) = 1 game Player1ID(55) + Player2ID(72) + Player4ID(90) = 1 game etc Any suggestions?
|
# ? Oct 29, 2017 04:45 |
|
Something like the following should get you the counts for maximal descriptions of the players in a game. There might be a faster or more elegant way; I haven't thought about it at length. And I haven't tested this, so it might need some adjusting. When I say maximal, I mean that if a game has players 1, 2 and 3 in it then it will be counted for '1,2,3' but not for '1,2' or '1,3' or '2,3'. I think you are better off extracting all the data and generating the output you describe using your favourite scripting language, rather than SQL. If there were a maximum number of players that could be in any given game, and if that maximum number were small, it might make it easier to do in SQL. code:
|
# ? Oct 29, 2017 23:45 |
|
skrath posted:Trying to figure out how to work out how get a combination of rows with MySQL. I actually have no idea where to start with this. I spent some time looking into this because I'm new to SQL and thought this would be good practice. I didn't find exactly the answer, but I'm pretty sure I found what comes one step right before the answer you're looking for. Here is what you can do to get all possible combinations of any two given PlayerIDs: code:
code:
I wasn't able to figure out how to implement that function, but I'm pretty sure that you could figure it out if you invest some time into it. I ran out of steam/interest, sadly. Maybe it's actually easier than that somehow. Maybe it could be possible to just add some kind of HAVING clause to the original thing and not need this RECURSIVE deal at all. I'm not experienced enough yet. edit-- Also before I close this Notepad window, here is how you can explicitly get the matching GameID for any two given PlayerIDs: code:
Love Stole the Day fucked around with this message at 14:21 on Oct 30, 2017 |
# ? Oct 30, 2017 14:11 |
|
Depending on the maximum number of players that can be in a game and the size of the userbase, neither of which we know, that approach might be impractical as it is O(n^p) where n is the number of users and p is the max players in a game.
|
# ? Oct 30, 2017 15:58 |
|
You'll want a recursive cte (so at least MySQL 8 or a better database) because your example target is"all combinations at least...". That is you count two players in a game even if there was a third player in your example. Ergo start with all games played by each player, then searching within that for a second player, then within those for the third, and so on. You'll need to track a few things to help the query along. Left as exercise for the reader. Soooo homework or hackerrank?
|
# ? Oct 30, 2017 23:27 |
|
Hammerite posted:Something like the following should get you the counts for maximal descriptions of the players in a game. There might be a faster or more elegant way; I haven't thought about it at length. And I haven't tested this, so it might need some adjusting. This is pretty much spot on, thank you! I tweaked it a little but I think I'm going to have to run it through a PHP script/loop to get the exact results I want (which is every combination of PlayerIDs) - I can do it by altering the GROUP BY and adding a WHERE clause between X PlayerIDs in the subquery, and making sure that it groups by team (instead of just GameID, as there are two teams playing in a game). Hammerite posted:Depending on the maximum number of players that can be in a game and the size of the userbase, neither of which we know, that approach might be impractical as it is O(n^p) where n is the number of users and p is the max players in a game. The PlayerLog table is 300,000 rows, so was hoping to do it all in SQL if possible. Max players per team per game is 15 on the roster. PhantomOfTheCopier posted:You'll want a recursive cte (so at least MySQL 8 or a better database) because your example target is"all combinations at least...". That is you count two players in a game even if there was a third player in your example. Neither -- working on a new algorithm/formula for team cohesion in my online basketball simulation. I wanted to take into account all times a player has played with any other player, as well as the whole group, to work out an algorithm to see how together the team is (before any 'chemistry' influences). Thanks everyone for your help; I'm pretty much a beginner at SQL and just hack my way using poorly thought out programming loops to get the data I want. Trying to do it all in queries is a skill I'm still trying to learn. skrath fucked around with this message at 00:29 on Oct 31, 2017 |
# ? Oct 31, 2017 00:13 |
|
|
# ? Jun 4, 2024 16:08 |
|
I kinda have to wonder what fifteen cross joins would do. It might be more efficient to group on games, construct combinations from that, then go back and count repeats between games.
|
# ? Oct 31, 2017 23:58 |