SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

nbv4: Aug 21, 2002; by Duchess Gummybuns

I'm working on a tagging scheme for my website. Basically I have one table full of blog entries, and another table of tags. Each row in the tag table has an blog_id that connects it to the blog it is tagging. Everything is working fine except for one thing. I can't figure out how I should go about edit/deleting/adding new tags to an blog. Lets say I have one blog that is tagged with three tags. Thats three rows in the tag table each with the corresponding blog_id. If I were to add an additional tag, I could just add a new row to that tags table, but what about if I want to delete one? Do I just delete the row? Wouldn't that hurt performance?

Also, how do I even determine if a its a new tag or not? I'm thinking of, whenever the user edits tags, to just delete all existing tags for that blog, then add the new ones, ensuring no duplicates exist. Is this a dumb idea? I can't think of any other way to do it.

nbv4 fucked around with this message at 07:47 on May 8, 2008

# ? May 8, 2008 01:06

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 20:52

Alex The Great: Oct 8, 2003; Lee Adama
Attorney at Law
As a former Viper Pilot, I know how to defend your rights!
From Genocide to Prostitution�
We have your six!

So at work, we're moving to SQLite to serve as our data format, away from XML. Today, our backend guys came back to me with a bunch a tables that don't use primary keys (but clearly could have) instead they using strings to identify different rows and such (albeit the repeated quite quickly). The effect of this was that in order to descern any of our data, we'd have to SELECT * and figure out the relationships ourselves, and I'm pretty sure that's not only dumb but the opposite of what we're trying to do with moving to SQLite in the first place.

Our eventual goal is to store a bunch of data that can either be laid out flat in a table format, or laid out in a tree structure, and the data sources are themselves incredibly varied. The backend team's initial solution was to make the main data table's column number completely variable - which sounded like a really bad idea (especially if a change comes down the pipeline later on to remove say, a "Month" column, which would require an ALTER, which we don't really even have access to in SQLite). This also, I feel complicates things further by not having a Primary Key because I'm afraid we'll have repeating data and so we'll never just delete one row, or update one row, etc etc.

Further, we'll want from time to time to represent this data as a tree structure, and I honestly have no idea how they want me to take this convoluted mess and put it into a tree.

So maybe you kind people can help me with some questions:
1) What is the best way to represent tree data within SQL? Has anyone ever had to do this? What did the table schema you have look like?
2) Generally, when looking to conserve memory usage and increase read times, is it superior to ask for large amounts of data with one SQL statement (Returns 100 rows), or do 10 Query's that each return 10 rows?
3) Performing ALTER on anything in SQLite is a bad idea and as such creating a mutable column scheme makes things really tricky - that being said, when is the time and place for this approach?

Thanks guys

# ? May 8, 2008 03:58

ImJasonH: Apr 2, 2004; RAMALAMADINGDONG!

Alex The Great posted:

So maybe you kind people can help me with some questions:
1) What is the best way to represent tree data within SQL? Has anyone ever had to do this? What did the table schema you have look like?

I've done trees in SQL two ways:

1.) A self-referencing foreign key in the child row pointing toward the parent row's primary key. This is an Adjacency List Tree (see: http://www.sitepoint.com/article/hierarchical-data-database ). This is pretty much the most sane way to do it, as far as I know.

2.) Trees with "left" and "right" columns called Modified Preorder Traversal Trees (see: http://www.sitepoint.com/article/hierarchical-data-database/2 ). This is supposedly a better way to handle hierarchical data, but it's a lot harder to write a query to find the direct children of a node. In fact almost every basic tree-mutation query is a lot harder to write (and thus more prone to bugs) than just using the simple parentId field. And adding a new node into the tree has the possibility of touching every other node.

There are probably other ways, and maybe I'm dumb for always using #1, but #2 just seems so counter-intuitive it makes a lot of simple queries a lot harder to write.

ImJasonH fucked around with this message at 04:57 on May 8, 2008

# ? May 8, 2008 04:45

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

ImJasonH posted:

I've done trees in SQL two ways:

1.) A self-referencing foreign key in the child row pointing toward the parent row's primary key. This is an Adjacency List Tree (see: http://www.sitepoint.com/article/hierarchical-data-database ). This is pretty much the most sane way to do it, as far as I know.

2.) Trees with "left" and "right" columns called Modified Preorder Traversal Trees (see: http://www.sitepoint.com/article/hierarchical-data-database/2 ). This is supposedly a better way to handle hierarchical data, but it's a lot harder to write a query to find the direct children of a node. In fact almost every basic tree-mutation query is a lot harder to write (and thus more prone to bugs) than just using the simple parentId field. And adding a new node into the tree has the possibility of touching every other node.

There are probably other ways, and maybe I'm dumb for always using #1, but #2 just seems so counter-intuitive it makes a lot of simple queries a lot harder to write.

#1. Has fast inserts and slow reads that get slower as your tree grows and the SQL is a lot simpler

#2. Has fast reads and slow inserts that potentially get slower as your tree grows, and the SQL is a lot more complex

I prefer #2, but the SQL does suck.

The trick for #2 is the put your sql for modifying the tree and reading from it into a class library, then test the poo poo out of it, then never touch it again. For me it turned into some sort of precursor technology where it just works but you don't know how and if you touch it will instantly self destruct.

Another solution for #2 is to create as set of rock solid stored procedures to handle it.

For a really really good explanation of both of these, along with sample SQL and other solutions, check out this book:

http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/ref=pd_bbs_sr_3?ie=UTF8&s=books&qid=1210248088&sr=8-3

# ? May 8, 2008 13:04

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Alex The Great posted:

2) Generally, when looking to conserve memory usage and increase read times, is it superior to ask for large amounts of data with one SQL statement (Returns 100 rows), or do 10 Query's that each return 10 rows?

Performing 1 query to get 100 rows is typically a lot better than doing 10 queries to get 10 rows. Reason being is that every query involves overhead which takes a lot more cpu and memory than just fetching those extra 90 rows which really don't take up that many resources.

If you are grabbing millions of rows, thats a different story.

# ? May 8, 2008 13:07

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

nbv4 posted:

I'm working on a tagging scheme for my website. Basically I have one table full of blog entries, and another table of tags. Each row in the tag table has an blog_id that connects it to the blog it is tagging. Everything is working fine except for one thing. I can't figure out how I should go about edit/deleting/adding new tags to an blog. Lets say I have one blog that is tagged with three tags. Thats three rows in the tag table each with the corresponding blog_id. If I were to add an additional tag, I could just add a new row to that tags table, but what about if I want to delete one? Do I just delete the row? Wouldn't that hurt performance?

Also, how do I even determine if a its a new tag or not? I'm thinking of, whenever the user edits tags, to just delete all existing tags for that blog, then add the new ones, ensuring no duplicates exist. Is this a dumb idea? I can't think of any other way to do it.

I would do the latter. Yeah, it seems gay to delete 5 rows then insert 5 new ones, but its be the simplest to code and is a pretty common solution. Having to compare the new tags with the old ones and try to figure out what changed and do an update here and a delete there is just a pain in the rear end. You aren't going to see a performance hit either unless this turns into the next myspace.

# ? May 8, 2008 13:13

Aredna: Mar 17, 2007; Nap Ghost

nbv4 posted:

I'm working on a tagging scheme for my website. Basically I have one table full of blog entries, and another table of tags. Each row in the tag table has an blog_id that connects it to the blog it is tagging. Everything is working fine except for one thing. I can't figure out how I should go about edit/deleting/adding new tags to an blog. Lets say I have one blog that is tagged with three tags. Thats three rows in the tag table each with the corresponding blog_id. If I were to add an additional tag, I could just add a new row to that tags table, but what about if I want to delete one? Do I just delete the row? Wouldn't that hurt performance?

Also, how do I even determine if a its a new tag or not? I'm thinking of, whenever the user edits tags, to just delete all existing tags for that blog, then add the new ones, ensuring no duplicates exist. Is this a dumb idea? I can't think of any other way to do it.

Here's something I've seen done that seems to me like it would be good for performance, but I don't really know so I'm sure someone here can point out the flaws in this method.

Add an additional field to the table to mark the fields as deleted, just a simple int or binary field to mark 1/0 or true/false. You can filter for this field to be false for your joins so that makes the queries simple to update. Once every so often during off-peak hours have a maintanence script set up to run and actually delete any rows marked as deleted from the table.

# ? May 8, 2008 19:27

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

What's the difference between a relationships table (relationship id + object ids) versus a table with composite primary keys?

I'm thinking, like... If it's just a join, where each object has its own properties, and only the relationships need to be defined, then a relationships table is fine. But then it's like, what if the relationships themselves have data that neither of the objects can provide independently, then you start attaching data onto the relationships table.

Now, this relationships table, do you implement yet another relationships xref table and then a table about the relationships, or do you just have a composite primary key in the relationships table (instead of a relationship id)?

code:

given:

fruits:        posters:
a | apples     t | triple tech
p | peaches    c | captain guy

this

xref:        relationships:
1 | t | a    1 | love the green ones
2 | t | p    2 | hate
3 | c | a    3 | it's okay

vs

relationships:
t | a | love the green ones
t | p | hate
c | a | it's okay

Edit: I think this is essentially questioning the artificiality of keys?

Triple Tech posted:

My company's web app let's customers run calculations that are Expensive. How can we mitigate this cost with caching?

Scenario 1 - The result of the calculation is of different sizes and dimensions each time, currently represented as a Perl data structure. Current solution, serialize it and store that string in the DB. Can it be done any other, better way?

Scenario 2 - The results of the calculations are the exact same size each time, maybe a different number of rows, but still the same columns no matter how you slice it. Can we just dump this data into a table and then make the PK somehow related to the inputs that the customer put in for the calculation? If already calculated, read cache, if not, calculate + store?

No takers on how to cache calculated data of varying sizes?

Triple Tech fucked around with this message at 20:07 on May 8, 2008

# ? May 8, 2008 20:01

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Aredna posted:

Here's something I've seen done that seems to me like it would be good for performance, but I don't really know so I'm sure someone here can point out the flaws in this method.

Add an additional field to the table to mark the fields as deleted, just a simple int or binary field to mark 1/0 or true/false. You can filter for this field to be false for your joins so that makes the queries simple to update. Once every so often during off-peak hours have a maintanence script set up to run and actually delete any rows marked as deleted from the table.

This is doubly good because it means that unlike normally unforgiving SQL DELETEs, you can undo this sort of delete, and also it gives you the chance to see historically what records were present in the table, instead of flat out losing them. My SQL professor in college always advised us to do deletes in this manner when possible.

# ? May 8, 2008 20:09

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Triple Tech posted:

code:

given:

fruits:        posters:
a | apples     t | triple tech
p | peaches    c | captain guy

this

xref:        relationships:
1 | t | a    1 | love the green ones
2 | t | p    2 | hate
3 | c | a    3 | it's okay

vs

relationships:
t | a | love the green ones
t | p | hate
c | a | it's okay

This is a question of normalization. Does the opinion relate solely to the relationship key, or is related to the poster combined with the fruit?

If it depends on the key of the relationship (i.e. you have a set of opinions that are reused among multiple posters) then the first solution is the correct one.

If it depends solely on the poster and their opinion of the fruit, then the answer is the second one.

# ? May 8, 2008 20:39

Sparta: Aug 14, 2003; the other white meat

I was told to read up on database theory/MySQL. I'm making a browser-based game. It's functionally pretty simple, and I thought I could code it from the seat of my pants, learning as I go, but people have said I should really look in to db theory before. Is there any online thing you would recommend I read?

# ? May 9, 2008 03:04

ImJasonH: Apr 2, 2004; RAMALAMADINGDONG!

Sparta posted:

I was told to read up on database theory/MySQL. I'm making a browser-based game. It's functionally pretty simple, and I thought I could code it from the seat of my pants, learning as I go, but people have said I should really look in to db theory before. Is there any online thing you would recommend I read?

The Wikipedia page for database normalization is surprisingly well put together. Read the sections on the first three normal forms, and the problems they solve.

# ? May 9, 2008 03:37

Grigori Rasputin: Aug 21, 2000; WE DON'T NEED ROME TELLING US WHAT TO DO

What's the proper way to output a string from a stored procedure in Transact-SQL(MS SQLServer 2000)? I am finishing up an ancient project for someone and am not too familiar with it.

I used RETURN before for integers and figured it would work with strings... haha, silly me. My procedure looks like this:

code:

alter  PROCEDURE [dbo].[sp_vGetName] 
	@id AS INTEGER,
	@name as nvarchar(255) out
AS
BEGIN
	SELECT @id= id
	FROM nametable
	WHERE name = @name

	RETURN @name
END

I then get an error when it attempts to convert @name to an integer... so how do I return a string value through an output param? Is there a better way to do this?

# ? May 12, 2008 21:12

Alex007: Jul 8, 2004

Grigori Rasputin posted:

What's the proper way to output a string from a stored procedure in Transact-SQL(MS SQLServer 2000)? I am finishing up an ancient project for someone and am not too familiar with it.

How do I return a string value through an output param? Is there a better way to do this?

Here's how to use an output parameter:

code:

CREATE PROCEDURE MyProc(@Param1 int, @Param2 int, @OutParam varchar(200) output) AS ...

Then, when you call the SP, you MUST pass it a variable and add the "output" keyword.

code:

DECLARE @MyOutParam varchar(200)
EXEC MyProc 1, 2, @MyOutParam output

Edit: Also, why are you trying to return in the same param that you received, without changing it at all ?

Alex007 fucked around with this message at 21:48 on May 12, 2008

# ? May 12, 2008 21:45

Grigori Rasputin: Aug 21, 2000; WE DON'T NEED ROME TELLING US WHAT TO DO

My bad, that's supposed to be "SELECT NAME FROM [table] where ID=x", I have it the other way around which makes no sense.

I'm using a .NET datasource control to run the stored procedure for use in an ASP.NET page. I have the param direction set to out in the control, but the out param ends up null everytime. I had it working for integers with an explicit RETURN, but those apparently don't work with strings.

# ? May 12, 2008 22:36

Xae: Jan 19, 2005

Another Oracle question here.

I'm still working on and off on a way to compare two generic objects in PL/SQL. gently caress this is a goddamn run around.

I devised a way of doing it by creating a massive overload clusterfuck (240 declarations, generated by another PL/SQL script) and letting Oracle determine the type through the overloading.

This method was vetoed by the client, with decent reasoning, there are issues that are popping up with the dynamic blind sorting, and the overloading.

They have instead decided to go with a Java method. While java is able to handle generic objects PL/SQL is not. I have been searching for a way to force some sort of serialization or somehow get into Oracle's kernal functions where they allow generics in PL/SQL (see the sys.ANYDATA and sys.ANYTYPE types for examples) however it appears that Oracle blocks access to those. Even for the java method they want there still has to be a PL/SQL procedure "mapped" to the Java method.

So, anyway: Does anyone know of a way to have a single function in PL/SQL that will accept any type of object? Or somehow get raw binary or a pointer or SOMETHING that will allow a generic type?

# ? May 13, 2008 02:11

chocojosh: Jun 9, 2007; D00D.

Grigori Rasputin posted:

My bad, that's supposed to be "SELECT NAME FROM [table] where ID=x", I have it the other way around which makes no sense.

I'm using a .NET datasource control to run the stored procedure for use in an ASP.NET page. I have the param direction set to out in the control, but the out param ends up null everytime. I had it working for integers with an explicit RETURN, but those apparently don't work with strings.

Google found me this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97795

Where I work we use the SqlHelper class (from Microsoft Application Blocks). While several years old, it's a great way to get what you need from Sql Server in much less code. See the article: http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

With SqlHelper, to retrieve a string bet would be to use SqlHelper.FillDataSet() method in C# and in Sql Server to "Select" the string you want to output.

*Waiting for victor to say how SqlHelper is horrible and inefficient

# ? May 13, 2008 03:38

Phummus: Aug 4, 2006; If I get ten spare bucks, it's going for a 30-pack of Schlitz.

I'll throw my question into the ring here to see if anyone can help.

I have (for simplicity sake) two tables: Users and Tasks.

Users has
nUsers_id INT
cUserName VARCHAR (30)

Taks has
nTasks_id INT
cDescription VARCHAR (1024)
nResponders_id INT
nAssignedTo_id INT

I need to write a query that will return the task description, responder name and assigned to name.

Right now I have

code:

SELECT TASKS.CDESCRIPTION,
       USERS.CUSERNAME    AS [RESPONDER],
       USERS.CUSERNAME    AS [ASSIGNEDTO]
FROM   TASKS
       INNER JOIN USERS
         ON USERS.NUSERS_ID = TASKS.NRESPONDERS_ID

How do I get the second name to appear in the query?

I'm using MS SQL Server 2005.

# ? May 13, 2008 14:47

Alex007: Jul 8, 2004

Re-join with the same table, and use an alias:

code:

SELECT TASKS.CDESCRIPTION,
       USERS.CUSERNAME    AS [RESPONDER],
       USERS2.CUSERNAME    AS [ASSIGNEDTO]
FROM   TASKS
       INNER JOIN USERS
         ON USERS.NUSERS_ID = TASKS.NRESPONDERS_ID
       INNER JOIN USERS AS USERS2
         ON USERS2.NUSERS_ID = TASKS.NASSIGNEDTO_ID

Also, hungarian notation :barf:

# ? May 13, 2008 14:58

cLin: Apr 21, 2003; lat3nt.net

This is a really newbie question which has been on my mind but when I create a field under a table and it asks for datatype and value, what does the value mean? For example, if I select datatype INT and value to be 255 does it mean it accepts integers up to 255?

# ? May 13, 2008 18:09

Code Jockey: Jan 24, 2006; 69420 basic bytes free

cLin posted:

This is a really newbie question which has been on my mind but when I create a field under a table and it asks for datatype and value, what does the value mean? For example, if I select datatype INT and value to be 255 does it mean it accepts integers up to 255?

I'm guessing it means default value for binding? Like if you don't supply a value it'll insert one. Not sure what tool you're using, but in SQL 2005 the two columns the table designer lists are name and type, and default value can be entered in the property area.

# ? May 13, 2008 19:02

cLin: Apr 21, 2003; lat3nt.net

I am using phpMyAdmin. It has a Length/Value field right after you select the data type.

# ? May 13, 2008 19:36

Alex007: Jul 8, 2004

cLin posted:

I am using phpMyAdmin. It has a Length/Value field right after you select the data type.

It's the size of the field.

It's clearer for char data types, it's the max number of chars you can fit in that field. For INT types, it's not the max value, it's the number of bytes the field has.

Leave it empty for INT data types, mysql know the right size according to your INT type selection.

Here's the table for int sizes:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

# ? May 13, 2008 19:49

Phummus: Aug 4, 2006; If I get ten spare bucks, it's going for a 30-pack of Schlitz.

Alex007 posted:

Re-join with the same table, and use an alias:

Awesome. Thank you so much. Now I'm on to my next oddity. I use a parameter defaulted to null in a stored procedure for each field in the table. Then I use isnull() to test if the parameter was passed in. If so, I match it in my where clause, and if not, I just match the field to itself. Like this:

code:

SELECT Field1, Field2
FROM Table1
WHERE @Field1 = isnull(@Field1, Table1.Field1)
AND @Field2= isnull(@Field2, Table1.Field2)

However, if Field2 is a varchar field and @field2 is null, the query returns no rows.

Any idea why?

# ? May 13, 2008 20:01

Alex007: Jul 8, 2004

MuppetPastor posted:

Awesome. Thank you so much. Now I'm on to my next oddity. I use a parameter defaulted to null in a stored procedure for each field in the table. Then I use isnull() to test if the parameter was passed in. If so, I match it in my where clause, and if not, I just match the field to itself. Like this:

However, if Field2 is a varchar field and @field2 is null, the query returns no rows.

Any idea why?

The correct way to do it would be:

code:

SELECT Field1, Field2
FROM Table1
WHERE (Table1.Field1 = @Field1 OR @Field1 IS NULL)
AND (Table1.Field2 = @Field2 OR @Field2 IS NULL)

# ? May 13, 2008 20:09

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

MuppetPastor posted:

Awesome. Thank you so much. Now I'm on to my next oddity. I use a parameter defaulted to null in a stored procedure for each field in the table. Then I use isnull() to test if the parameter was passed in. If so, I match it in my where clause, and if not, I just match the field to itself. Like this:
code:
SELECT Field1, Field2
FROM Table1
WHERE @Field1 = isnull(@Field1, Table1.Field1)
AND @Field2= isnull(@Field2, Table1.Field2)
However, if Field2 is a varchar field and @field2 is null, the query returns no rows.

Any idea why?

First of all that is a really weird solution....

But to try and answer your question. How are you getting the data into the stored procedure? I am guessing that the null is getting converted to an empty string from which isnull would return the second parameter. Empty string is not equal to null.

# ? May 13, 2008 20:11

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Alex007 posted:

It's the size of the field.

It's clearer for char data types, it's the max number of chars you can fit in that field. For INT types, it's not the max value, it's the number of bytes the field has.

Leave it empty for INT data types, mysql know the right size according to your INT type selection.

Here's the table for int sizes:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Oh loving duh. :doh:

How did that not occur to me...

# ? May 13, 2008 20:12

Phummus: Aug 4, 2006; If I get ten spare bucks, it's going for a 30-pack of Schlitz.

Begby posted:

First of all that is a really weird solution....

But to try and answer your question. How are you getting the data into the stored procedure? I am guessing that the null is getting converted to an empty string from which isnull would return the second parameter. Empty string is not equal to null.

Like this for example:

code:

ALTER PROCEDURE up_get_test
(@field1 VARCHAR(20) = NULL,
@field2 VARCHAR(20) = NULL)
AS
SELECT Field1, Field2
FROM Table1
WHERE Field1 = isnull(@field1, field1)
AND field2 = isnull(@field2, field2)

This won't return any rows in which field1 or field 2 IS NULL

Phummus fucked around with this message at 20:22 on May 13, 2008

# ? May 13, 2008 20:13

Alex007: Jul 8, 2004

MuppetPastor posted:

code:
...
WHERE Field1 = isnull(@field1, field1)
AND field2 = isnull(@field2, field2)
This won't return any rows in which field1 or field 2 IS NULL

It's because NULL <> NULL (they are not equal).

So when @field1 is NULL, SQL sees this

code:

WHERE NULL = isnull(NULL, NULL)

Which is:

code:

WHERE NULL = NULL

And it's false.

# ? May 13, 2008 20:27

derdewey: Dec 3, 2004

Using mysql 5 something. Okay, a question about limit magic.

So I have the table factors which is linked to items. How can I select all the factors but only select 5 of their items? Factors usually have like a gazillion items related with 'em but I only want 5. I also need to randomize which 5 those are, but I need to know the first part before I worry about that.

code:

SELECT * FROM factors INNER JOIN items ON factors.id = items.factor_id LIMIT 5;

Wahh, I don't want to have to run a factor number of queries to do this... but I may just have to

# ? May 14, 2008 04:38

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

It's kinda tricky, but it is possible. The way I'd do it is to assign each factor/item combination a rank, and then choose only those with a rank <= 5.

Let's say for now that you don't want to randomize items, and that instead you want the 5 latest items (ordered by the "date" field). Your SQL might look like this:

code:

SELECT factors.id, items.id, (
        SELECT COUNT(*) + 1
        FROM items AS i2
        WHERE i2.factor_id = factors.id
          AND items.date > i2.date
        ) AS rank
   FROM factors
   INNER JOIN items ON factors.id = items.factor_id
   WHERE rank <= 5
   ORDER BY factors.id, rank

The sub-select works out the ranking of each row (with the latest date as rank 1). The WHERE clause only selects the items with rank <= 5, so you only get the "top" 5 items per factor.

I don't know if MySQL allows you to re-use a SELECT sub-query in the WHERE clause, you might have to duplicate the sub-query in the WHERE clause where I've put "rank".

So that gets you 5 rows per factor, but they'll always be the same 5 rows. The next step is to randomize which rows in items are ranked. That's a tricky one, since SQL doesn't really work like that. You have no control over the order of rows in which the SQL expressions will be evaluated over. SQL usually relies on its expressions being idempotent (input of X results in output of Y no matter what point in time it's called). So I guess you really want a function such that given an item and a random seed, it will hash the two together to generate a unique and non-random value that's "rankable", so you can draw out the top 5 items. Perhaps md5("random string" + item.id) might be enough (where "random string" is constant, but changes every time you make the SQL call).

# ? May 14, 2008 06:41

EssOEss: Oct 23, 2006; 128-bit approved

I love GUIDs! Having a unique ID for every object is very useful and makes me happy.

However, the sad reality is that you cannot have infinite variation in a finite variable - the logical conclusion is that GUID collisions can and do happen.

How should I handle them? Should I handle them? I think I should. I mean, if I have two information producers that create objects and a conflict happens, some undesirable links might form in some situations.

This is certainly nothing to worry about in everyday operation. But nevertheless... it worries me.

# ? May 14, 2008 08:14

Filburt Shellbach: Nov 6, 2007; Apni tackat say tujay aaj mitta juu gaa!

EssOEss posted:

I love GUIDs! Having a unique ID for every object is very useful and makes me happy.

However, the sad reality is that you cannot have infinite variation in a finite variable - the logical conclusion is that GUID collisions can and do happen.

I wouldn't worry so much about it. Quoth the Wikipedia:

Wikipedia posted:

For example, consider the observable universe, which contains about 5*10^22 stars; every star could then have 6.8*10^15 universally unique GUIDs.

# ? May 14, 2008 08:17

and0r: Mar 30, 2005; Cheese it!

I have a table of transactions, and a table of date ranges...

code:

table: Transactions
Trans_ID    Trans_Date
----------------------
1001        2007-11-02
1002        2008-03-16
1003        2008-04-08

table: TermDates
ID          StartDate       EndDate
--------------------------------------
1           2008-01-29      2008-02-28
2           2008-03-12      2008-04-04
3           2008-04-28      2008-06-04

How can I construct a query that will only return transactions that have occurred during a term? This is in MS Access, so nothing fancy.

# ? May 15, 2008 08:28

Aredna: Mar 17, 2007; Nap Ghost

and0r posted:

I have a table of transactions, and a table of date ranges...
code:
table: Transactions
Trans_ID    Trans_Date
----------------------
1001        2007-11-02
1002        2008-03-16
1003        2008-04-08

table: TermDates
ID          StartDate       EndDate
--------------------------------------
1           2008-01-29      2008-02-28
2           2008-03-12      2008-04-04
3           2008-04-28      2008-06-04
How can I construct a query that will only return transactions that have occurred during a term? This is in MS Access, so nothing fancy.

The easiest way will be to do it with a script in SQL View so that your join conditions aren't limited to just equality tests.

code:

SELECT T.TRANS_ID
FROM   TRANSACTIONS T
       INNER JOIN TERMDATES TD
         ON T.TRANS_DATE >= TD.STARTDATE
            AND T.TRANS_DATE <= TD.ENDDATE
WHERE  TD.ID = '2'

Edit: This is probably a better way to write it and Access should be able to display it in Design View as well.

code:

SELECT T.TRANS_ID
FROM   TRANSACTIONS T,
       TERMDATES TD
WHERE  TD.ID = '2'
       AND T.TRANS_DATE >= TD.STARTDATE
       AND T.TRANS_DATE <= TD.ENDDATE

Aredna fucked around with this message at 15:21 on May 15, 2008

# ? May 15, 2008 14:44

Dromio: Oct 16, 2002; Sleeper

This seems pretty small. I imported some legacy data into a SQL Server database. I want to add a uniqueidentifier column to that table, and populate it with random GUIDS. I've added the column, but how to I populate it with random GUIDs for the existing data?

# ? May 15, 2008 21:50

Code Jockey: Jan 24, 2006; 69420 basic bytes free

Dromio posted:

This seems pretty small. I imported some legacy data into a SQL Server database. I want to add a uniqueidentifier column to that table, and populate it with random GUIDS. I've added the column, but how to I populate it with random GUIDs for the existing data?

Off the top of my head, I'd say write a stored procedure to walk through the data using a cursor, generate a new GUID for each row, and update the row with it?

# ? May 15, 2008 22:32

Ardhanari: Mar 23, 2006

Dromio posted:

This seems pretty small. I imported some legacy data into a SQL Server database. I want to add a uniqueidentifier column to that table, and populate it with random GUIDS. I've added the column, but how to I populate it with random GUIDs for the existing data?

You can set the default value of the column to NEWID() in the table designer or the ALTER TABLE script, and it should add everything the way you want it.

# ? May 16, 2008 00:04

Dromio: Oct 16, 2002; Sleeper

Thanks, the NewID() function was what I really needed.

# ? May 16, 2008 14:39

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 20:52

other people: Jun 27, 2004; Associate Christ

Wow I am dumb.

I just altered a varchar field to binary blob. Do I have to restore a backup now to get that data back?

edit: I restored my backup from mysql with the command line. It worked, but all my special characters are hosed. Anything with an accent or umlaut etc is now a nasty looking é thing.

Two questions now:

1. Luckily, the columns with these special characters were not converted to blobs, and I made a copy of the hosed up db before I overwrote it with the backup. So now I just need to copy data from a specific column in the hosed up table to the same column in the restored table. What would sql for such an operation look like?

Is this close?
replace into new_table select column_a from old_table where old_table.id = new_table.id

2. How do I export/import data in a way so that will not have this problem? I had a similar result when I exported data from a mysql db on a different host and then imported on a new host. When I imported the data all the fields were truncated whenever it hit an accented character. For example, if I had a value of V�rit�s et mensonges I would just have V after the import. Really friggin' annoying!

other people fucked around with this message at 23:20 on May 18, 2008

# ? May 18, 2008 17:59

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »