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 »

atb: Mar 20, 2009

In additon to having the URLs seperated by a "," I also need to have then encased in " ' ", so to be processed they need to read be passed onto the RSS parser as 'http://www.url1.com', 'http://www.url2.com',

So I changed the code to read as:

$feeds = new SimplePie (array ("'".$row['URL']."',"));

The problem is that I end up again with the inital error message I was having. It is omitting the "'http:" portion of the URL. The error message is:

Warning: file_get_contents(//www.cbssports.com/nfl/teams/syndicate/ARI',) [function.file-get-contents]: failed to open stream: No such file or directory

The URL field type is set as TEXT, Collation is latin1_swedish_ci, and the SHOW CREATE TABLE gives:

CREATE TABLE `rssfeeds` (
`TeamID` varchar(150) NOT NULL,
`URL` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

# ? Dec 7, 2009 06:10

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 06:14

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

That seems bizarre, why would it cut out the http:// part? Maybe the code for SimplePie is doing something funny. Can you post that?

# ? Dec 7, 2009 11:30

Wonderbread 2000: Oct 27, 2008

Got a problem with a stored procedure I'm using to send some HTML email. Here's the relevant part of the code:

code:

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @taskHTML =
		N'<H4>Outstanding Inspections</H4>' +
		N'<table cellspacing="10">' +
		N'<tr><th>Facility</th><th>Inspection</th><th>Date Due</th></tr>' +
		CAST((SELECT td = FacilityName, '', td = InspectionName, '', td = CONVERT(VARCHAR(8),DueDate,1) FROM viewTaskMail 
                WHERE UserKey = @userKey ORDER BY DueDate, FacilityName FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +
		N'</table>'

		SET @issueHTML =
		N'<H4>Outstanding Issues</H4>' +
		N'<table cellspacing="10">' +
		N'<tr><th>Issue ID</th><th>Issue</th><th>Date Due</th></tr>' +
		CAST((SELECT td = IssueID, '', td = IssueName, '', td = CONVERT(VARCHAR(8),DueDate,1) FROM viewIssueMail 
                WHERE UserKey = @userKey ORDER BY DueDate, FacilityName FOR XML PATH('tr'), TYPE) AS VARCHAR(MAX)) +
		N'</table>'

		SET @bodyHTML = @issueHTML + @taskHTML
		exec msdb.dbo.sp_send_dbmail
			@profile_name = 'IDOT-EMIS Mailer',
			@recipients = @userEmail,
			@subject = 'IDOT-EMIS Reminder Email',
			@body = @bodyHTML,
			@body_format = 'HTML'
		FETCH NEXT FROM MailCursor INTO @userKey, @userEmail
	END

This actually works just fine unless one of those queries returns 0 rows, in which case it sends the user a blank email. Is there a way to do exception handling or something to prevent that from happening?

# ? Dec 7, 2009 21:08

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

code:

		SET @bodyHTML = isnull(@issueHTML,'') + isnull(@taskHTML,'')

		IF @bodyHTML <> ''
			exec msdb.dbo.sp_send_dbmail
				@profile_name = 'IDOT-EMIS Mailer',
				@recipients = @userEmail,
				@subject = 'IDOT-EMIS Reminder Email',
				@body = @bodyHTML,
				@body_format = 'HTML'

# ? Dec 7, 2009 21:49

Wonderbread 2000: Oct 27, 2008

That works great, thanks.

Why are those null if the query comes back with no results though? I expected them to contain the table with those headers, just no actual data.

# ? Dec 8, 2009 17:00

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

Wonderbread 2000 posted:

That works great, thanks.

Why are those null if the query comes back with no results though? I expected them to contain the table with those headers, just no actual data.

NULL is not the empty string. Anything plus NULL is NULL.

# ? Dec 8, 2009 17:31

onionradish: Jul 6, 2006; That's spicy.

I've got a simple SQLITE table with five columns:

code:

[item_list]
id, item_name, item_type, item_date, item_seq

I want to assign a number from 1 to n (where n=number of rows in the query result) to `item_seq` based on the date order in a query like:

code:

SELECT * FROM item_list WHERE item_type='1' ORDER BY item_date;

so that I end up with a table like this:

code:

id		item_name		item_type		item_date		item_seq
1		Foo			1			2009-10-05		1
8		Foobar			1			2009-10-17		2
6		Bar			1			2009-11-13		3
3		Barfoo			1			2009-12-01		4

Items are added/deleted daily, so the `item_seq` values change. The script that updates the `item_seq` currently loops through an UPDATE ... SET for each `id` returned in the initial query. There are 700+ entries in that query, so performance isn't terrible YET (a couple of seconds), but it will get worse over time.

Is there a smarter way to rewrite the UPDATE before I hit performance issues?

# ? Dec 9, 2009 18:25

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

Why would you need a column like that?

# ? Dec 9, 2009 18:36

No Safe Word: Feb 26, 2005

Yeah, why even store such a column? You could effectively recreate it with a view if you want and then it wouldn't have to be updated every time an item was added or deleted. Or you use that same stored procedure you'd use for the view to just return the table with that column tacked on.

# ? Dec 9, 2009 18:41

onionradish: Jul 6, 2006; That's spicy.

Maybe the column CAN be calculated directly through a view/query. I'm dumb and trying to learn! :downs:

My GoogleFu may also be weak; searching for something that would do the equivalent for SQLite yielded a fairly ugly nested SELECT with COUNT that took 15-20 seconds to run.

# ? Dec 9, 2009 19:23

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

onionradish posted:

Maybe the column CAN be calculated directly through a view/query. I'm dumb and trying to learn!

My GoogleFu may also be weak; searching for something that would do the equivalent for SQLite yielded a fairly ugly nested SELECT with COUNT that took 15-20 seconds to run.

Yes it most certainly be done faster at your application level then at the sql side i assume all you want the the number in sequence of the row?

# ? Dec 9, 2009 19:49

onionradish: Jul 6, 2006; That's spicy.

Sprawl posted:

Yes it most certainly be done faster at your application level then at the sql side i assume all you want the the number in sequence of the row?

All I actually need to add is the row number. Depending on how this gets implemented, there may be some real-time resorting (such as by item_name or reverse date), but the original row values (item_seq) wouldn't need to be re-calculated from the original query.

If there's no magic SQLite to make that column, I can populate it in the script after the query. Thought there might be some magic code to do bulk updates or within the SQLite query.

edit: The olde-tyme flatfile originally contained all data, including this sequence number. It's since been moved to a database so scripts and/or database are needing new capabilities.

# ? Dec 9, 2009 20:01

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

onionradish posted:

All I actually need to add is the row number. Depending on how this gets implemented, there may be some real-time resorting (such as by item_name or reverse date), but the original row values (item_seq) wouldn't need to be re-calculated from the original query.

If there's no magic SQLite to make that column, I can populate it in the script after the query. Thought there might be some magic code to do bulk updates or within the SQLite query.

edit: The olde-tyme flatfile originally contained all data, including this sequence number. It's since been moved to a database so scripts and/or database are needing new capabilities.

Yes well its very easy at application time to add an incremental index sequence for most OO languages these days. Or maybe you could select insert into a temporary table?

# ? Dec 9, 2009 20:09

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

I'm still not sold. What are you using this value for?

# ? Dec 9, 2009 22:15

Comatoast: Aug 1, 2003; by Fluffdaddy

This is a homework question. I'm not looking for you or anyone to do my homework for me, but I do need a push in the correct direction.

My final project consists of creating a relational database in mysql. It is a database of publications. Each publication has a list of attributes (id, title, journal, pages, year) and a set of authors. The authors can number from zero to an infinite number per publication.

So, how do I go about creating a table which can hold an infinite number of authors?

fletcher posted:

Sorry, but that is a really dumb question. Go buy an infinite number of hard drives? What class is this?

Bah. You're right. I'm just going to say 100 is the max and go from there. I know how to do that.

Comatoast fucked around with this message at 00:26 on Dec 10, 2009

# ? Dec 10, 2009 00:12

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Comatoast posted:

So, how do I go about creating a table which can hold an infinite number of authors?

Sorry, but that is a really dumb question. Go buy an infinite number of hard drives? What class is this for?

# ? Dec 10, 2009 00:15

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Comatoast posted:

Bah. You're right. I'm just going to say 100 is the max and go from there. I know how to do that.

You don't need to say infinite or even 100, just say "arbitrary number of authors" or something.

You need three tables, publication, author, and a table to relate the two to each other.

If this whole class was about databases and you can't do this by the end of the semester...well...start putting effort into school and stop wasting your parents money?

# ? Dec 10, 2009 00:31

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Comatoast posted:

This is a homework question. I'm not looking for you or anyone to do my homework for me, but I do need a push in the correct direction.

My final project consists of creating a relational database in mysql. It is a database of publications. Each publication has a list of attributes (id, title, journal, pages, year) and a set of authors. The authors can number from zero to an infinite number per publication.

So, how do I go about creating a table which can hold an infinite number of authors?

Bah. You're right. I'm just going to say 100 is the max and go from there. I know how to do that.

This is really easy and i shouldn't give it to you but you sound a little slow, think second normal.

CREATE TABLE `publication` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`journal` varchar(255) DEFAULT NULL,
`pages` varchar(10) DEFAULT NULL,
`year` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `publication_author` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`publicationid` int(11) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# ? Dec 10, 2009 00:46

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Sprawl posted:

This is really easy and i shouldn't give it to you but you sound a little slow, think second normal.

Boooooooo! You should have let him figure this poo poo out.

# ? Dec 10, 2009 00:48

Comatoast: Aug 1, 2003; by Fluffdaddy

code:

CREATE TABLE publication
(
        publication_id INTEGER NOT NULL AUTO_INCREMENT,
        title VARCHAR(255) DEFAULT NULL,
        year INTEGER DEFAULT NULL,
        journal VARCHAR(255) DEFAULT NULL,
        pages VARCHAR(11)DEFAULT NULL,
        PRIMARY KEY(publication_id)
)


CREATE TABLE author
(
        name VARCHAR(255) DEFAULT NULL,
        author_id INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(author_id)
)


CREATE TABLE author_group
(
        group_id INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(group_id)
)


CREATE TABLE writes
(
        publication_id INTEGER NOT NULL AUTO_INCREMENT,
        group_id INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (publication_id  , group_id),
        FOREIGN KEY (publication_id) REFERENCES publication,
        FOREIGN KEY (group_id) REFERENCES author_group
)

Would something like this work as well? It looks like your two table approach would be easier to work with.

edit: Thanks for the help guys. I went with the two table approach and its coming together nicely.

Comatoast fucked around with this message at 00:08 on Dec 11, 2009

# ? Dec 10, 2009 01:20

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Here are some possible changes to the posted DDL:

quote:

CREATE TABLE `publications` (
~~`id` int(11) NOT NULL AUTO_INCREMENT,~~
ISBN char(whatever) PRIMARY KEY NOT NULL,
/* Publications have a real key, the ISBN or whatever. note: int(11) assumes there can be ten billion publications */

`title` varchar(255) NOT NULL ~~DEFAULT NULL~~,
/* 255 seems large; anyway I bet there are existing industry standards. better to look for these instead of using arbitrary sizes. Title should obviously be NOT NULL */

`journal` varchar(255) DEFAULT NULL /*?*?,
/* Look for an existing industry key. If none exist, 255 seems a bit wide as well. Can journal info be missing? Hopefully it won't be and the column can be NOT NULL */

`pages` ~~varchar(10)~~ int ~~DEFAULT NULL~~ NOT NULL
CHECK( pages BETWEEN 1 AND <some reasonable upper bound> ),
/* pages is numeric, right? can we assume some page info is missing?*/

~~`year`~~ publication_year SMALLINT~~datetime~~ ~~DEFAULT NULL~~ CHECK(publication_year BETWEEN <reasonable lower limit> AND <reasonable upper limit>,
/*
1. "YEAR" is a reserved word in SQL.
2. This column, according to the requirements is a year value, not a datetime. Perhaps use SMALLINT value with a CHECK constraint to set the bounds.
3. Do we know that some pub years will be missing? Ideally we want a NOT NULL column. */

~~PRIMARY KEY (`id`)~~ /* use ISBN or whatever the industry standard is */
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `publication_authors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
/* if there already is an industry key column to represent author_name, we don't want to use an autoincrement column. it is redundant. the real key is something like (ISBN, author_id) */

~~`publicationid` int(11) DEFAULT NULL~~,
/* This _must_ be NOT NULL by definition, and needs a FOREIGN KEY constraint */
ISBN varchar(whatever) NOT NULL FOREIGN KEY REFERENCES publications(ISBN),

`author` varchar(255) DEFAULT NULL, /* 255 seems a bit high */
/* This is a person's name, so see if we want to store the name parts (first, last, middle, etc.) And look for an industry key as well. */

~~PRIMARY KEY (`id`)~~
PRIMARY KEY(ISBN, author_id <or name columns> )
/* even if an autoincrement column will be used for joins, there _must_ be a unique key on real data values to even be in first normal form. no duplicated data */
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Bad Titty Puker fucked around with this message at 02:38 on Dec 10, 2009

# ? Dec 10, 2009 02:34

Wreckus: Dec 15, 2007; From birth, man carries the weight of gravity on his shoulders. He is bolted to earth. But man has only to sink beneath the surface and he is free.

How do you increment a sequence in oracle? I need to set aside about 15 numbers in the sequence for my own use I want to set the sequence 15 numbers higher so it doesn't try to use my numbers.

My Google-fu is just returning how to create sequences with specific increments :<

# ? Dec 10, 2009 21:01

var1ety: Jul 26, 2004

Wreckus posted:

How do you increment a sequence in oracle? I need to set aside about 15 numbers in the sequence for my own use I want to set the sequence 15 numbers higher so it doesn't try to use my numbers.

My Google-fu is just returning how to create sequences with specific increments :<

You can either create it with an initial offset of 15, manually increment it 15 times, or alter an existing sequence.

Increment it 15 times (or run the 'select from dual' 15 times).

code:

select myseq.nextval from dual connect by level <= 15;

See Oracle's SQL Reference manual for information on CREATE SEQUENCE or ALTER SEQUENCE at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm. The create statement would be along the lines of:

code:

create sequence myseq start with 16;

# ? Dec 10, 2009 21:17

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

How do I import the contents of a text file into a specific field of a table in MySQL?

I have a bunch of TXT files with descriptions that I need to import to the same fields of multiple records.

I can't figure out how to use mysqlimport this way. If mysqlimport is the way to do it.

(I can figure out how to write the batch script to import them all. I just can't figure out the syntax to get one file inserted into the field.)

# ? Dec 11, 2009 18:45

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

Agrikk posted:

How do I import the contents of a text file into a specific field of a table in MySQL?

I have a bunch of TXT files with descriptions that I need to import to the same fields of multiple records.

I can't figure out how to use mysqlimport this way. If mysqlimport is the way to do it.

(I can figure out how to write the batch script to import them all. I just can't figure out the syntax to get one file inserted into the field.)

Whats in the text files? It sounds like you are going to need some scripting with python or perl or something to do it since you will probably need to escape the data if it has special characters or formatting.

# ? Dec 11, 2009 19:04

Agrikk: Oct 17, 2003; Take care with that! We have not fully ascertained its function, and the ticking is accelerating.

Begby posted:

Whats in the text files? It sounds like you are going to need some scripting with python or perl or something to do it since you will probably need to escape the data if it has special characters or formatting.

It's a long description of the record. Basically a really long text file that'll fit in a MEMO field in my database. At the moment it's just raw text, but I'll go back in and add some html tags to make it readable when it's called from a web page.

# ? Dec 11, 2009 21:29

Little Brittle: Nov 1, 2004; Come visit me dawg

I am helping with an ecommerce site that will be using a drop-ship supplier. This supplier offers a product feed with 60,000 SKUs for inventory data that is updated every 10 minutes. When a product is discontinued, it disappears from the feed. There are no timestamps for updated products.

I'm trying to figure out the best MySQL database design that allows for quick inventory updates every 10 minutes, and also has some sort of mechanism for removing items that no longer appear in the feed. The bad way would be to set all stock to zero before importing and all items that exist in the feed would overwrite with their stock quantities. The problem with that is there are 60k SKUs, and anyone trying to place an order while the feed is processing would get a cart error during checkout.

What is the proper way to do this? My goal is to get the lowest possible processing time and avoid 120k SQL queries every 10 minutes, while making sure that discontinued items are removed ASAP.

# ? Dec 14, 2009 19:59

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

Why don't you just delete the item from the table? If you want to keep a table of product information and also keep track of stock (which may be zero), make a new table.

What exactly is the bottleneck here? That seems like such a small table.

Anyway, here's a rule of thumb. Anything you do to optimize reads will make writes slower (if you need to do lots of real time writes like Amazon). Conversely, anything you do to optimize writes will make reads slower.

# ? Dec 14, 2009 21:41

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

I assume you have some sort of transactional table, where you would be able to do something like a lock and a set of crap no?

You could start with a lock then and update setting all the inventories to 0 and then run a set of updates for each inventory item with the correct levels and then unlock the table.

# ? Dec 14, 2009 22:32

Sneaking Mission: Nov 11, 2008

If you want to avoid recalculating all of the entries you can have a delta table with all of the inventory changes since the last update. If you only sold 3 units of item 'A', you would be able to process the delta table and only update that single row.

# ? Dec 14, 2009 22:37

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

In the grand scheme of things 60,000 records is nothing and should be able to be processed in 10 seconds or less if you set it up right i have a setup that does this every hour it drops about 250,000 records and inserts them in about 15 seconds.

# ? Dec 15, 2009 00:10

Little Brittle: Nov 1, 2004; Come visit me dawg

Triple Tech posted:

Why don't you just delete the item from the table? If you want to keep a table of product information and also keep track of stock (which may be zero), make a new table.

What exactly is the bottleneck here? That seems like such a small table.

I need to keep old products in the database for reporting purposes. I'm only looking to change the product status to unavailable if it doesn't appear in the feed. My problem is finding the most logical way to select which products exist in the database but do not exist in the latest feed. I realize this isn't a huge amount of data, but we are working with limited server resources and I'd rather not be running hundreds of thousands of queries every 10 minutes.

Sprawl posted:

I assume you have some sort of transactional table, where you would be able to do something like a lock and a set of crap no?

You could start with a lock then and update setting all the inventories to 0 and then run a set of updates for each inventory item with the correct levels and then unlock the table.

That was my first thought, I was just hoping for ways to achieve the same effect without having to run an update query for every SKU in the feed. Ideally, I'd like to find out a way to do this that only ran an update on SKUs whose inventory has changed and products which no longer exist in the feed. I'm not too hot with the ins and outs of table locking. If someone attempts to place an order while the inventory table is locked, won't that trigger an error?

dcallen posted:

If you want to avoid recalculating all of the entries you can have a delta table with all of the inventory changes since the last update. If you only sold 3 units of item 'A', you would be able to process the delta table and only update that single row.

That gets me thinking. I could create 2 tables in addition to the master product table, called 'inventory_in' and 'inventory_out'. I could populate the tables with the most recent feed and the one previous to it. I can select inventory changes, new items, and removed items easily by comparing the two, while not touching the master product table with hundreds of thousands of old SKUs.

Or am I just retarded and way over-thinking this?

Little Brittle fucked around with this message at 00:18 on Dec 15, 2009

# ? Dec 15, 2009 00:12

MoNsTeR: Jun 29, 2002

Little Brittle posted:

What is the proper way to do this?

Get your feed provider to give you update timestamps like a sane person.

I know that's not really helpful but it's the correct answer to your question.

# ? Dec 16, 2009 17:11

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

Is there a "cleaner" way to do this?

A DB of tracks in which they are children of collections, and in the case of multi-cd albums, each collection is the child of the album collection. I need the root collection for a given track.

code:

SELECT
	p.id AS track,
	pp.id AS album
FROM 
	products p,
	products pp
WHERE
	p.entity_type = 'Track' AND
	pp.id = 
		(CASE WHEN 
			(
			(SELECT ppp.parent_fk FROM products ppp WHERE p.parent_fk = ppp.id) 
			IS NOT NULL
			)
		THEN 
			(SELECT ppp.parent_fk FROM products ppp WHERE p.parent_fk = ppp.id)
		ELSE 
			p.parent_fk 
		END);

E: This is cleaner, but I wonder if there's yet a better way

code:

SELECT
	p.id AS track,
	pp.id AS album
FROM 
	products p,
	products pp
WHERE
	p.entity_type = 'Track' AND
	pp.id = 
	(SELECT 
		(CASE WHEN (ppp.parent_fk IS NULL) THEN ppp.id ELSE ppp.parent_fk END)
	FROM products ppp WHERE p.parent_fk = ppp.id);

Carthag Tuek fucked around with this message at 16:39 on Dec 18, 2009

# ? Dec 18, 2009 16:01

Lamb-Blaster 4000: Sep 20, 2007

I have a sort of table of contents I need to pull from the database and order it by page ascending, however page is a varchar because some page items appear in the prefix on pages numbered with roman numerals, so a simple

code:

order by page asc

returns

code:

+------+------------------------------------+
| page | name                               |
+------+------------------------------------+
| 1    | Iryna Bondarevska and Larysa Dovha |
| 21   | Roman Mnich                        |
| 33   | Maria Vasilieva                    |
| 47   | Werner Korthaase                   |
| 73   | Maryna Tkachuk                     |
| 83   | Iryna Valiavko                     |
| IX   | Roman Senkus                       |
+------+------------------------------------+

tldr: I need to be able to order by page asc where letter pages appear first asc followed by numbered pages asc.

# ? Dec 19, 2009 01:04

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

ORDER BY PAGE REGEXP '[a-zA-Z]+' DESC, PAGE

# ? Dec 19, 2009 02:28

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Carthag posted:

Is there a "cleaner" way to do this?

A DB of tracks in which they are children of collections, and in the case of multi-cd albums, each collection is the child of the album collection. I need the root collection for a given track.
code:
SELECT
	p.id AS track,
	pp.id AS album
FROM 
	products p,
	products pp
WHERE
	p.entity_type = 'Track' AND
	pp.id = 
		(CASE WHEN 
			(
			(SELECT ppp.parent_fk FROM products ppp WHERE p.parent_fk = ppp.id) 
			IS NOT NULL
			)
		THEN 
			(SELECT ppp.parent_fk FROM products ppp WHERE p.parent_fk = ppp.id)
		ELSE 
			p.parent_fk 
		END);
E: This is cleaner, but I wonder if there's yet a better way
code:
SELECT
	p.id AS track,
	pp.id AS album
FROM 
	products p,
	products pp
WHERE
	p.entity_type = 'Track' AND
	pp.id = 
	(SELECT 
		(CASE WHEN (ppp.parent_fk IS NULL) THEN ppp.id ELSE ppp.parent_fk END)
	FROM products ppp WHERE p.parent_fk = ppp.id);

I would say that Tracks, Albums, and Collections are different entities and would look into storing them in separate tables. If there is a many-to-many relationship between Tracks and Albums, or Albums and Collections, I would look into making tables for those as well. So getting the collection that contains an album that contains a track is straightforward normal joins.

# ? Dec 19, 2009 07:23

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Lamb-Blaster 4000 posted:

I have a sort of table of contents I need to pull from the database and order it by page ascending, however page is a varchar because some page items appear in the prefix on pages numbered with roman numerals, so a simple
code:
order by page asc
returns
code:
+------+------------------------------------+
| page | name                               |
+------+------------------------------------+
| 1    | Iryna Bondarevska and Larysa Dovha |
| 21   | Roman Mnich                        |
| 33   | Maria Vasilieva                    |
| 47   | Werner Korthaase                   |
| 73   | Maryna Tkachuk                     |
| 83   | Iryna Valiavko                     |
| IX   | Roman Senkus                       |
+------+------------------------------------+
tldr: I need to be able to order by page asc where letter pages appear first asc followed by numbered pages asc.

If you need the roman numerals to sort in their order (I, II, III, IV, etc.) you may want to create a lookup table that contains the roman numeral (unique) and the sort key (unique, starts with 1, increases monotonically). Do an outer join on the numeral and add an offset to the sort key so the Roman numerals come after the Arabic ones.

# ? Dec 19, 2009 07:27

Carthag Tuek: Oct 15, 2005; Tider skal komme,
tider skal henrulle,
sl�gt skal f�lge sl�gters gang

camels posted:

I would say that Tracks, Albums, and Collections are different entities and would look into storing them in separate tables. If there is a many-to-many relationship between Tracks and Albums, or Albums and Collections, I would look into making tables for those as well. So getting the collection that contains an album that contains a track is straightforward normal joins.

Not an option, I should say that this is not my own database.

I'm just looking for a better query.

# ? Dec 19, 2009 13:02

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 06:14

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Hammerite posted:

ORDER BY PAGE REGEXP '[a-zA-Z]+' DESC, PAGE

Agh, I only just realised this won't sort the roman numerals properly. :saddowns:

Yeah, better just go with some variation on what camels said.

# ? Dec 19, 2009 15:31

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

«‹›303 »