|
quote:
|
# ? May 14, 2013 16:01 |
|
|
# ? Jun 8, 2024 07:46 |
|
This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER):code:
|
# ? May 15, 2013 03:18 |
|
Scaramouche posted:This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER): Generate that list of available ids, and select the top 1 ordered by newid()?
|
# ? May 15, 2013 05:59 |
|
Nth Doctor posted:Generate that list of available ids, and select the top 1 ordered by newid()? Scaramouche posted:This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER): As per Nth Doctor, compact it all down to: SELECT TOP 1 * FROM Table WHERE Criteria = @blah ORDER BY NEWID() That is a kind of unexpected nice trick of GUID generation. It's not that good for performance in huge tables though.
|
# ? May 15, 2013 18:55 |
|
Malcolm XML posted:I'm assuming that 33N30 means 33 degrees 30 minutes North so just look up the formula for DMS to decimal (which I think is D + M/60 + S/3600 but don't rely on that) and then use the sign depending on direction (+ for N/E and - for S/W) DMS is degrees minutes seconds. I see two out the the three max in 33N30
|
# ? May 15, 2013 22:30 |
|
Finer resolution is optional. Taking an example from the sitecode:
|
# ? May 16, 2013 03:00 |
I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database. How would I be able to store at least a couple thousands animals maintaining their taxonomical info (class > order > family > genus > species)? A Lion would be: Mammalia > Carnivora > Felidae > Panthera > Panthera leo (Lion). I want to be able to click on Felidae and get everything under it including cats, tigers, etc. I was checking out adjacency lists but I'm not sure it would be a good idea to put everything into one table, but maybe I'm wrong?
|
|
# ? May 16, 2013 03:36 |
|
gmq posted:I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database. Adjacency lists They cost a bit to update because you wind up touching a lot of rows but access time is stupid fast because you're performing a range search on an index. Everything in one nested set table suits the usage you described, we're using one at work for something similar except it has dynamic depth so we're also using a dynamically generated unpivot in a stored proc to turn it into a table with one column per level . e: vvvv This is true but you'd wind up putting all the hierarchy management code in the application, and I personally dislike big keys because they lead to a fair bit of duplication if you wind up trying to foreign key to them. e2: In defense of adjacency lists, they don't need you to update on average 50% of the table when you manipulate a record. Night Shade fucked around with this message at 04:52 on May 16, 2013 |
# ? May 16, 2013 04:30 |
|
Class / Order / Family and company are amazing natural keys. Unless you can think of any reason you'd want to put them outside of the table with a surrogate key then there's no harm whatsoever in just lumping the entire taxonomic name in the single table and dealing with the hierarchy in the application code.
|
# ? May 16, 2013 04:31 |
|
I'd probably do it something like:code:
e: beaten
|
# ? May 16, 2013 04:35 |
|
Scaramouche posted:This is another kind of an academic question, since I've already done it but I was wondering if there's a better/different way. I basically need to choose a random record id from a pool of non-incremental ids. This means that I can't just go random range, since each ID has to be valid. What I ended up doing was getting a count of all possible ids, generate a random within that range, and then select the ROW_NUMBER within the counted range, like so (if this looks familiar I've jammed together two very popular yet separate tutorials on random numbers and ROW_NUMBER): I would expect a true min/max for lower/upper to be faster (generate the random ID in the same way) if coupled with SELECT * FROM T WHERE id>=random ORDER BY id LIMIT 1 ?
|
# ? May 16, 2013 12:30 |
|
gmq posted:I'm starting to learn a bit of (relational) database design for a project but I'm having problems getting my head wrapped around getting hierarchical info inside a SQL database. You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx The set of many to one tables is the traditional way, HierarchyIds would offer the ability to do hierarchy traversal functions in TSQL.
|
# ? May 16, 2013 19:56 |
|
Molotov Cock Tale posted:You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx
|
# ? May 17, 2013 04:32 |
|
I have a simple select routine that works great on my local machine with MySQL Workbench. However, the same procedure on phpMyAdmin returns only the first result when I'm expecting 9. Running the same select query directly does exactly what I want, and the routine when called in Workbench returns the same values. What weird poo poo is phpMyAdmin doing? It's for a homework assignment so I have to use phpMyAdmin. e: Oh wow, of course after I post the question on SA, the answer shows up a few google searches later after it's been racking my brain for 20 minutes. If anyone else has this issue, just call the procedure manually in the SQL pane and it will work. Macichne Leainig fucked around with this message at 06:36 on May 17, 2013 |
# ? May 17, 2013 06:33 |
i barely GNU her! posted:I'd probably do it something like: This was actually my first thought but I'm not sure how well it would perform considering that there's probably a reason why nested sets and adjacency lists are a thing. Molotov Cock Tale posted:You say SQL but not which RDBMS, if you're on SQL Server 2008/2012 one thing to take a look at might be the new HierarchyId data type: http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx Linux server so I can't use SQL server. However, postgreSQL seems to have a similar thing with ltree, does anyone here have experience with that?
|
|
# ? May 18, 2013 17:43 |
|
Are you trying to optimize that solution before checking to see whether it actually needs to be optimized? Don't you think that's a bit ... premature?
|
# ? May 19, 2013 01:45 |
Jabor posted:Are you trying to optimize that solution before checking to see whether it actually needs to be optimized? True. I guess I just wanted to know if there was a 'right' way to do it.
|
|
# ? May 19, 2013 01:48 |
|
gmq posted:This was actually my first thought but I'm not sure how well it would perform considering that there's probably a reason why nested sets and adjacency lists are a thing.
|
# ? May 19, 2013 01:55 |
|
Sedro posted:Those let you represent hierarchies and graphs with unbounded depth, heterogeneous nodes, cycles, etc. Schemas which are notoriously non-performant in all modern databases. You shouldn't want to be using nested sets or adjacency lists and fortunately you don't have to! How would you represent a tree structure with a db then if you do want to be performant? Off-load the complexity to whatever language connects to the db, using specific db extensions, or what?
|
# ? May 19, 2013 13:21 |
|
KARMA! posted:How would you represent a tree structure with a db then if you do want to be performant? Off-load the complexity to whatever language connects to the db, using specific db extensions, or what? If your database supports recursive queries, you'd just have a table of relationships (an adjacency list) and run recursive queries on that. If you don't, it depends on your use case - for a lot of cases (e.g. checking for friend-of-a-friends), just faking the recursion yourself out to a small depth (say, 4) could be entirely sufficient and still performant enough. Or if your data forms a tree and needs to be queried often but not updated very much you might use nested sets. In general though, if you find yourself running into these problems it's often a sign that you need to re-think your data model. Or perhaps your storage implementation - if your data fits better as a graph than as a set of relations, why not use a graph database instead of a relational one?
|
# ? May 19, 2013 13:57 |
I need a bit of help on understanding how indexing happens when doing a join across tables. Suppose I have the following query:SQL code:
X-BUM-RAIDER-X fucked around with this message at 12:16 on May 21, 2013 |
|
# ? May 21, 2013 12:08 |
|
OBAMA BIN LinkedIn posted:I need a bit of help on understanding how indexing happens when doing a join across tables. Suppose I have the following query: Use EXPLAIN ANALYZE (postgres) or whatever the equivalent is for your DB and it should output what the query planner has constructed. From a similar query I ran it will likely pull the where clauses inside the join and filter before running if it can prove that the result is the same (which is basically true for the relational algebra but not for sql in general). Of course there's always some stupid edge case that comes up that breaks the ability of the planner to take shortcuts.
|
# ? May 21, 2013 12:21 |
|
I have the following table: 1. Integer Key (primary key) 2. Varchar Name (indexed) 3. Integer Size (indexed) 4. Blob Data Given maxSize, I want to select all Data such that Size <= maxSize; however, I also only want one Data per Name, and given two rows with identical Names I want the Data from the row with the greatest Size (such that Size <= maxSize). For example, with maxSize = 50 and given the rows {1, name1, 35, data1} {2, name1, 45, data2} {3, name1, 55, data3} I want {data2}; for the same maxSize, given the rows {1, name1, 35, data1} {2, name2, 50, data2} {3, name3, 55, data3} I want {data1, data2} Right now I'm just selecting all rows with Size <= maxSize and then selecting the correct Data from the resultset in application code, but it seems like there should be a way to do this in SQL that I'm missing code:
|
# ? May 21, 2013 16:54 |
|
This is a classic application for analytical/windowing functions, assuming the DB you're using supports them. What RDBMS are you using? For SQL Server 2005+ you'd want something like: code:
|
# ? May 21, 2013 20:25 |
|
I'm using MySQL, so I'm guessing it'll be something likecode:
|
# ? May 21, 2013 20:41 |
|
1. Get all ticket holders (Table 'sales') 2. Match ticket holders with guests by a JOIN on CWID (for the ticket holders table 'sales') and seniorCWID (for guest table 'guests') Does this require a sub-select? Right now just running an inner join gets me the ticket holders who -have- guests, but I want the ticket holder information AND the guest information edit: nevermind, got what I wanted using a simple join Fluue fucked around with this message at 23:14 on May 21, 2013 |
# ? May 21, 2013 22:50 |
|
MySQL question here, god my comprehension of joins is abysmal. Given Table A {id} and Table B {id, value} where B.id is a foreign key referencing A.id, how can I construct a query that will pull all rows from A that either have no record in B or do have a record in B but have a B.value of X? Above Our Own fucked around with this message at 00:37 on May 26, 2013 |
# ? May 26, 2013 00:34 |
|
Above Our Own posted:MySQL question here, god my comprehension of joins is abysmal. SQL code:
|
# ? May 26, 2013 01:28 |
|
Should really be "WHERE b.id IS NULL", to account for the case where b exists and has a NULL contained in value, which doesn't want to be selected by the criteria given. (Though in practice this distinction is unlikely to matter.)
|
# ? May 26, 2013 02:29 |
|
roomforthetuna posted:Should really be "WHERE b.id IS NULL", to account for the case where b exists and has a NULL contained in value, which doesn't want to be selected by the criteria given. (Though in practice this distinction is unlikely to matter.) Oh, you're right. I guess I inferred that the columns were all NOT NULL, but there's nothing in the question to support that.
|
# ? May 26, 2013 03:30 |
|
I could have sworn I've done this in the past but I can't find anything in my notes, and Google is coming up with solutions that split lists into columns, not process a list into rows. I'm working on MSSQL 2008. What I basically want to do is convert a single value INSERT statement from:code:
code:
|
# ? May 27, 2013 23:01 |
|
Not that I know anything about MERGE but I think it will work for you.
|
# ? May 27, 2013 23:38 |
|
I'm new to mysql and I am trying to do something like this but I don't know how...code:
for an item_id in old_items is 2, I want to set the corresponding item's new flag to true.
|
# ? May 28, 2013 00:49 |
|
You probably just have to re-state the query so you are not updating two tablesSQL code:
|
# ? May 28, 2013 01:35 |
|
Scaramouche posted:
What are you using for the split function? I think you'll want a table valued function and then you'd select from it. Something like... code:
|
# ? May 28, 2013 08:25 |
|
Scaramouche posted:I could have sworn I've done this in the past but I can't find anything in my notes, and Google is coming up with solutions that split lists into columns, not process a list into rows. I'm working on MSSQL 2008. What I basically want to do is convert a single value INSERT statement from: Why not create a view that selects from your function, and use that in your insert? Is your function table valued or scalar?
|
# ? May 28, 2013 13:13 |
|
Can anyone see why this join does not return any results from the subquery? http://privatepaste.com/146024dbb3 Edit: Could it be because I didn't select any of the columns from it? celestial teapot fucked around with this message at 20:55 on May 31, 2013 |
# ? May 31, 2013 20:50 |
|
The main SQL guy is on vacation and my background is in semiconductors, so this could be a truly question. I wrote a query which retrieves the following character string: code:
code:
|
# ? May 31, 2013 23:02 |
|
SQL code:
|
# ? May 31, 2013 23:06 |
|
|
# ? Jun 8, 2024 07:46 |
|
Pardot posted:
Holy hell that was fast -- really appreciate the effort. Unfortunately, I'm working in MS SQL Server Management Studio, and it doesn't seem to recognize split_part functions. Is there a way to import the functions or approximate them using vanilla SQL?
|
# ? Jun 1, 2013 00:47 |