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 »

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

If a facility has access to all commodities in a category then Option B is better but I'd call the second table just 'facilities_has_categories' to be clearer.

It's better because most changes will only require one of your bridge tables to be modified, whereas a change in option A will typically require at least two of the bridge tables to be synchronously modified for the data to be consistent.

# ? Nov 7, 2014 22:39

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 15:25

kiwid: Sep 30, 2013

Some facilities have access to some categories (maybe all but never none), and some commodities (maybe all but never none).

If a facility has access to a category, they do not necessarily have access to all commodities in that category, and vice versa, if a facility has access to a commodity, they may not have access to all categories that that commodity belongs to.

Does that mean I should be using the second schema?

# ? Nov 7, 2014 22:54

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

I think so, but 'facilities_has_categories_has_commodities' should be joining directly to 'categories' and 'commodities' rather than to 'categories_has_commodities'.

# ? Nov 7, 2014 23:12

kiwid: Sep 30, 2013

Like this?

I didn't even realize you could do that, I thought you had to join two tables to one many to many. Didn't realize you can do a many-to-many for three tables.

# ? Nov 8, 2014 01:16

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

kiwid posted:

MySQL

edit: Here is how I'm currently doing it. Is this a good way to do it?

I don't know that I can answer without sounding utterly biased. First, I knew you were going to say MySQL... probably based on the wording in the original question. In all honesty, you might as well just make a single varchar(random number) like every other MySQL user and call it good, because everything is likely to be handled, ultimately, in the application, so why bother with all that noise in the database if it doesn't gain you anything.

Now then, one choice is to store the data that way, or encoded in such a way that the application does most of the interpretation with no effect on database performance because you aren't running optimized queries over those fields anyway. Encoding could be anything from a blob to custom formats, markup, etcetera. This pushes all the integrity off to the application. Indeed, one of the main issues with your proposal is integrity: Nothing prevents all three from being non-null.

As there is no programmatic union (a la C, "store multiple things in the same space"), you either need to use a database that's custom and gives it to you, or, lacking that, if you decide you must have a union type, you can create a custom data type. In that case, as in your proposed structure, you'd really need to store another field identifying the actual type. This is no different than a union, where the data stored is fundamentally useless for decoding purposes unless you know whether it's an integer, float, character, pointer, whatever. Presumably, you would then be able to select on specific types, or wrap CASE statements around each field to enforce NULL based on the registered type for that row. Even if the application "accidentally" loaded (different) values into all three fields, you'd have a unique result for the row.

A third approach is to separate the fields into their own tables, only inserting a referenced row into the one appropriate table. While this provides some optimization on queries for subtypes, it seems a bit ridiculous for the application you've described. It seems highly unlikely that your primary use case is "searching for test_dec between 0.73 and 0.81", whence it's unlikely that you'd even much care about indexing. If all queries you perform are already restricted to groups/classes/quarters, a table scan of the relevant rows will be fast enough.

Please don't tell us this was a homework question.

# ? Nov 8, 2014 05:36

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

kiwid posted:

Like this?

I didn't even realize you could do that, I thought you had to join two tables to one many to many. Didn't realize you can do a many-to-many for three tables.

Yep, exactly. But don't think of it as joining three tables via many-to-many. Think of it as one 'master' table joining many-to-one to a reference table, three times.

By the way, your original categories->commodities table is still likely to be useful as a master reference of which commodities fall into which categories. It just won't get involved in this particular set of records about which commodities are at which facilities and which categories they're in at that facility.

# ? Nov 8, 2014 05:43

kiwid: Sep 30, 2013

PhantomOfTheCopier posted:

I don't know that I can answer without sounding utterly biased. First, I knew you were going to say MySQL... probably based on the wording in the original question. In all honesty, you might as well just make a single varchar(random number) like every other MySQL user and call it good, because everything is likely to be handled, ultimately, in the application, so why bother with all that noise in the database if it doesn't gain you anything.

Now then, one choice is to store the data that way, or encoded in such a way that the application does most of the interpretation with no effect on database performance because you aren't running optimized queries over those fields anyway. Encoding could be anything from a blob to custom formats, markup, etcetera. This pushes all the integrity off to the application. Indeed, one of the main issues with your proposal is integrity: Nothing prevents all three from being non-null.

As there is no programmatic union (a la C, "store multiple things in the same space"), you either need to use a database that's custom and gives it to you, or, lacking that, if you decide you must have a union type, you can create a custom data type. In that case, as in your proposed structure, you'd really need to store another field identifying the actual type. This is no different than a union, where the data stored is fundamentally useless for decoding purposes unless you know whether it's an integer, float, character, pointer, whatever. Presumably, you would then be able to select on specific types, or wrap CASE statements around each field to enforce NULL based on the registered type for that row. Even if the application "accidentally" loaded (different) values into all three fields, you'd have a unique result for the row.

A third approach is to separate the fields into their own tables, only inserting a referenced row into the one appropriate table. While this provides some optimization on queries for subtypes, it seems a bit ridiculous for the application you've described. It seems highly unlikely that your primary use case is "searching for test_dec between 0.73 and 0.81", whence it's unlikely that you'd even much care about indexing. If all queries you perform are already restricted to groups/classes/quarters, a table scan of the relevant rows will be fast enough.

Please don't tell us this was a homework question.

Definitely isn't a home work question

This is more of a personal learning project that happened to get put into Protoduction. The application is actually quite large and it's going to be expanded really soon. What' I've shown in this thread is really only a small fraction of the app and database. What I'm doing now is basically refactoring the application since I've learnt better programming practices and I'm trying to learn better database design so that in a couple months, when the planned expansion to this application starts being defined, I have a better base to work off of.

I think your third approach sounds interesting and I would be searching for values that are out of the min and max specifications so thanks for giving me these options to look into.

Curious though, why do you have a bias against MySQL? I assume you're a MSSQL guy? I really don't know much about other database platforms, is there a reason MySQL shouldn't be used?

kiwid fucked around with this message at 17:35 on Nov 9, 2014

# ? Nov 9, 2014 17:33

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

kiwid posted:

Curious though, why do you have a bias against MySQL? I assume you're a MSSQL guy? I really don't know much about other database platforms, is there a reason MySQL shouldn't be used?

MySQL sucks poo poo for numerous reasons that you can find all over the internet. A lot of it only applies to older versions of MySQL but the reality is that unless you're in full control of your database then you're stuck with whatever lovely versions your hosting provider has installed. But even once you actually configure it to behave like a real database you'll find that it's features are lagging behind every comercial database like MSSQL, Oracle, and even Postgres. Another big point of contention with using MySQL in enterprise is that most drivers for it are GPL licensed so you can't redistribute any software that uses it without also making it GPL. To get around that Oracle requires you to fork over lots of cash to license a driver with a proprietary license that you can then redistribute with your app. IMO for almost every application you're better off using Postgres as your free database of choice when starting a new project.

Examples of poo poo you'll encounter in MySQL:
https://stackoverflow.com/questions/4206261/java-method-halts-when-retrieving-java-sql-date-object-from-mysql-database-why
A utf-8 implementation that isn't really utf-8. You can now use utf8mb4 to get fully unicode support, but it's probably not configured that way out of the box and of course the old uft8 is still there as a gotcha for all newcomers.
A real post with some meat about pros and cons of MySQL
And there is something I can't find about how MySQL doesn't use InnoDB for it's internal tables so that transactions that you think have been committed are actually corrupted.

# ? Nov 10, 2014 19:36

Pardot: Jul 25, 2001

Janitor Prime posted:

Examples of poo poo you'll encounter in MySQL:

You didn't link the best one: http://grimoire.ca/mysql/choose-something-else

quote:

Thesis: databases fill roles ranging from pure storage to complex and interesting data processing; MySQL is differently bad at both tasks. Real apps all fall somewhere between these poles, and suffer variably from both sets of MySQL flaws.

* MySQL is bad at storage.
* MySQL is bad at data processing.
* MySQL is bad by design.
* Bad arguments for using MySQL.

# ? Nov 10, 2014 20:43

kiwid: Sep 30, 2013

Hmm, I never realized that. Why is it the default in a LAMP/LEMP stack and why is it so popular?

# ? Nov 10, 2014 21:48

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Free, large install base, lots of document, not Microsoft/Oracle. Free.

# ? Nov 10, 2014 21:58

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

kiwid posted:

Hmm, I never realized that. Why is it the default in a LAMP/LEMP stack and why is it so popular?

It had a default of caching writes instead of doing disk commits for the longest time, so people got this idea that it was fast. I admit that it is still relatively fast for reads... for simple/flat queries. Give it a few joins and you'd best go get some pizza.

edit: ^^^^ There's not much in that list to separate it from other free databases. Regarding documentation, I find MySQL stuff to be just awful. Let's compare:

http://dev.mysql.com/doc/refman/5.7/en/create-table.html
http://www.postgresql.org/docs/9.3/static/sql-createtable.html
http://msdn.microsoft.com/en-us/library/ms174979.aspx
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#i2095331 (yay railroad diagrams!)
https://www.sqlite.org/lang_createtable.html (yay railroad diagrams!)

Okay, I prefer EBNF, but railroad diagrams are kinda cute too.

PhantomOfTheCopier fucked around with this message at 01:25 on Nov 11, 2014

# ? Nov 11, 2014 00:04

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

I think the syntax was a lot more forgiving as well, and more similar to T-SQL than PostGres's version.

# ? Nov 11, 2014 00:12

Xae: Jan 19, 2005

kiwid posted:

Hmm, I never realized that. Why is it the default in a LAMP/LEMP stack and why is it so popular?

Because most developers are bad at databases.

Back in the day there the two big free databases where MySQL and PostgreSQL. MySQL was really fast when it is running under no load and doing simple queries. It would return your 10 row query in .01 seconds. PostgreSQL would return the same query in .05 seconds. But PostgreSQL would remain stable under load and would keep your data intact.

Developers looked at the results and said "MySQL is fives times as fast as PostgreSQL!!!" and ran with it. Only once their application start making GBS threads the bed due to MySQL playing fast and lose with data integrity did people start to take a second look at how it got so fast. But by that time MySQL was the established candidate. Google and Sun dumped tens of millions of dollars into unfucking MySQL and managed to make it reliable.

Edit: And to make things worse MySQL was only faster when not under load. I remember reading benchmarks as far back is ~2005 that showed PostgreSQL beating MySQL once the database was under a decent load.

Xae fucked around with this message at 00:17 on Nov 11, 2014

# ? Nov 11, 2014 00:14

karms: Jan 22, 2006; by Nyc_Tattoo; Yam Slacker

Way back when I stared, wamp was the default choice. No, scratch that, it was the only choice. Yeah you had other databases but they cost money and I'm a literal child with no income. Yeah I could run linux I guess but that would mean dual booting in to a black screen that did nothing. Ywah I could have gone with perl but that required a ton of arcane knowledge that I did not care to find. Mysql was a proper database to me; It uses sql with tables and relationships, that was good enough for me!

Only much later do I even hear of postgres, and started to understand why mysql is so bad at being a database. I always know it was bottom-barrel stuff, but I did not run in circles where this was well known. Ah, to be young again. :allears:

karms fucked around with this message at 01:21 on Nov 11, 2014

# ? Nov 11, 2014 01:15

kiwid: Sep 30, 2013

We do have MSSQL in the office but it's 2005. This place tends to severely lag behind in purchasing software and licensing. So it looks like I'll start looking into PostgreSQL.

# ? Nov 11, 2014 14:18

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

kiwid posted:

We do have MSSQL in the office but it's 2005. This place tends to severely lag behind in purchasing software and licensing. So it looks like I'll start looking into PostgreSQL.

Go find some magazine where it's been featured and leave it open to that page on some non-IT manager's desk, or point out how it's so widely used "they had to support it in RDS" or something. If you try to get it adopted on technical merits, you'll spend all your time fielding questions like, "Doesn't everyone use MySQL?". The answer is of course not.

As to getting started, find some spare equipment somewhere, slap on Linux (with a non-memory-hog window manager, if you must), and work through the basic install guides. As you'll like install from a package, all you need to do is initialize the cluster and create a user. If you get stuck in the usual first-timer problem, check for appropriate settings in pg_hba.conf.

Oh yeah, and go read about the text types.

... More later.

# ? Nov 11, 2014 14:44

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

kiwid posted:

We do have MSSQL in the office but it's 2005. This place tends to severely lag behind in purchasing software and licensing. So it looks like I'll start looking into PostgreSQL.

MSSQL 2005 is not a terrible database, all things considered, although you are missing a fair number of useful features with it. Not that I'm pushing MS by any means, but we went through the same thing with DB choice, and we ended up sticking with MSSQL after working out that developer migration costs were less than an upgraded SQL license.

# ? Nov 11, 2014 16:01

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Xae posted:

And to make things worse MySQL was only faster when not under load. I remember reading benchmarks as far back is ~2005 that showed PostgreSQL beating MySQL once the database was under a decent load.

Another thing to consider is when this battle happened. MySQL rose to popularity in the very late 90s and early 00s. The world was a different place back then.

MySQL was easy to set up, reasonably fast, had lots of little convenience features, and had bindings for many languages. Postgres was adequately fast, but was a real bear to set up and was weirdly arcane. User management still requires config file editing. The low barrier to entry made MySQL popular, just as it made PHP popular. The two are everywhere now.

The Sun and then Oracle acquisitions put a huge dent in MySQL's progress. There are now numerous forks, each competing for developer attention. Meanwhile, Postgres has continued to evolve and now easily beats MySQL in features, stability and performance. The only thing Postgres isn't good at is dealing with shared hosting environments.

If you're building a new application and aren't going to target shared hosting, then it's going to be a better idea to start with Postgres now.

# ? Nov 11, 2014 19:06

Malcolm XML: Aug 8, 2009; I always knew it would end like ｔｈｉｓ．

kiwid posted:

We do have MSSQL in the office but it's 2005. This place tends to severely lag behind in purchasing software and licensing. So it looks like I'll start looking into PostgreSQL.

jesus christ sign up for Azure or something. MSSQL 2005 is ancient.

# ? Nov 13, 2014 17:33

Withnail: Feb 11, 2004

Being the sql idiot that I am, I'm stuck on flattening out my data model. Do I need to use pivot and stuff to do this, or is there a simpler way that I'm missing.

Master_Code_Table
MasterCodeId
Category1Id
Category2Id

Category1_Table
Category1Id

Category2_Table
Category2Id

Child_Code_Table
ChildId
MasterId

Code_Table
CodeId
ChildId

Category2 Category2
Category1 Code Code Code Code
Category1 Code Code Code Code

# ? Nov 17, 2014 17:01

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

Withnail posted:

Being the sql idiot that I am, I'm stuck on flattening out my data model. Do I need to use pivot and stuff to do this, or is there a simpler way that I'm missing.

If I understand what you're looking for correctly, which is hard to know since something ate your formatting, then what is Child_Code_Table for? And what is Code_Table related to? Your hierarchy is Code => Child_Code => Master (which is somehow not the parent) => Category1/2? What is this meant to accomplish, and what do you gain by having relations all over this schema?

Ideally you want it to look like a family tree. That is, if you see a straight line down to the next layer with no branches, something isn't right there.

# ? Nov 17, 2014 23:05

Shmoogy: Mar 21, 2007

I've been trying to understand this all day and it seems like it should be so easy.

I have two tables, Results:

and Catconvert:

I am trying to basically do a vlookup where, for each row in Results Table, I lookup the producttypes(yellow color), and grab the googleproductcat(blue color) from the Catconvert table. I need to do this for the other blanks as well - but I can figure it out once I know how to do it for a single result. I would then like to set this value into the appropriate row in the results table.

The closest I've gotten is this:

I think it's kind of close maybe... but it's returning 11.4 million rows, when the results table only has 91k entries in it.

This is trivial with a vlookup or index/match in excel - I've only been using sqlite for one day now, so I'm sorry if I'm using terms incorrectly. I thought this was the sort of thing that SQL is amazing at, but it's not very intuitive to me -- JOIN is as close as I've gotten.

e: Got it - this is the query I needed:

code:

select producttyperesult, googleproductcategory, conversioncstlbl0, conversionadwordslabels, conversioncstlbl1, conversioncstlbl2, sid from Results INNER JOIN Catconverts on Results.producttyperesult = Catconverts.producttype

I had to change some columns because of ambiguous columns

Shmoogy fucked around with this message at 03:25 on Nov 19, 2014

# ? Nov 19, 2014 02:46

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I've got a big old switched proc (MSSQL) that is basically:

code:

@SwitchBy varchar(100)
AS
IF SwitchType='blah'
 BEGIN
  SELECT * FROM cattes
  WHERE
  (always the same criteria)
  AND
  (different criteria depending on @SwitchBy)
 END

Repeated about 8 times. Recently I've had to update (always the same criteria) and it's a pain in the rear end to do it 8 times; is there a way to preserve the set of WHERE clauses so they're written once, and then be able to dynamically choose the second set based on what @SwitchBy is?

# ? Nov 19, 2014 03:07

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Scaramouche posted:

I've got a big old switched proc (MSSQL) that is basically:
code:
@SwitchBy varchar(100)
AS
IF SwitchType='blah'
 BEGIN
  SELECT * FROM cattes
  WHERE
  (always the same criteria)
  AND
  (different criteria depending on @SwitchBy)
 END
Repeated about 8 times. Recently I've had to update (always the same criteria) and it's a pain in the rear end to do it 8 times; is there a way to preserve the set of WHERE clauses so they're written once, and then be able to dynamically choose the second set based on what @SwitchBy is?

Get good at using sp_executesql.

code:

@SwitchBy varchar(100)
AS

DECLARE @Statement NVARCHAR(MAX) = N'SELECT * FROM cattes
  WHERE
  (always the same criteria)';
DECLARE @Foo INT = 42, @Bar VARCHAR(100) = '23';

IF SwitchType='blah'
 BEGIN
  SELECT @Statement = @Statement + N'
  (different criteria depending on @SwitchBy)';
 END;

EXEC sp_executesql @Statement, N'/*You can put parameter names here like:*/ @Foo INT, @Bar VARCHAR(100), ...', @Foo, @Bar, ...;

Nth Doctor fucked around with this message at 06:50 on Nov 19, 2014

# ? Nov 19, 2014 06:47

Erwin: Feb 17, 2006

Sorry about the tangential question, but this seems the best place to ask. Is anyone familiar with Aqua Data Studio? A new developer used it at his last job and would like to use it here, and I'm all for new and better tools, but it looks like hot garbage from 1998.

We use MSSQL, and SSMS + Redgate has been enough for everyone else. But, if Aqua Data Studio is actually useful, I'm happy to get it for him. Any thoughts?

# ? Nov 19, 2014 15:09

KennyG: Oct 22, 2002; Here to blow my own horn.

I know I'm not a regular of this thread, but I have gotten a lot of great help here.

I thought I would cross-post an opening my company has that I posted in the Job thread.
If you are interested I will save you half a click, it's a lead/sr Microsoft SQL DBA position in Washington DC or Chicago, NY, London, Zurich or Bangalore India.
Flexible on location if you are awesome and don't want to move.

http://forums.somethingawful.com/showthread.php?threadid=3075135&pagenumber=77#post437879596

# ? Nov 19, 2014 16:58

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

Nth Doctor posted:

Get good at using sp_executesql.

Alternately, and I really hate to suggest this because it often results in total garbage code, CASE WHEN statements work in WHERE clauses.

# ? Nov 19, 2014 20:55

BlackMK4: Aug 23, 2006; wat.; Megamarm

I don't know where else to post this (if anyone has a better thread let me know...) but I'm dealing with hundreds of PDF pages of standardized tabular data that I need to move to csv -> SQL db.
The pages look like this:

Right now I'm using Tabula (http://tabula.nerdpower.org) and exporting one table as CSV at a time with a filename of the diagram ie. 001 in this case. I then use a shell script to batch insert two columns to the end of each line, combine all of the CSV files into one, try importing it to my db, and finally correct the few issues by hand. It takes about 45 minutes to process one of these PDFs and get it correctly inserted into the DB. There must be a more efficient way since I've got some 200 PDFs. :downs:

# ? Nov 20, 2014 20:14

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

BlackMK4 posted:

I don't know where else to post this (if anyone has a better thread let me know...) but I'm dealing with hundreds of PDF pages of standardized tabular data that I need to move to csv -> SQL db.
The pages look like this:

Right now I'm using Tabula (http://tabula.nerdpower.org) and exporting one table as CSV at a time with a filename of the diagram ie. 001 in this case. I then use a shell script to batch insert two columns to the end of each line, combine all of the CSV files into one, try importing it to my db, and finally correct the few issues by hand. It takes about 45 minutes to process one of these PDFs and get it correctly inserted into the DB. There must be a more efficient way since I've got some 200 PDFs.

I would recommend using SSIS, but I don't know if you're on MSSQL. Script Task to automate and page scrape to a flat file, then a data flow from the flat file to your DB.

# ? Nov 20, 2014 21:39

BlackMK4: Aug 23, 2006; wat.; Megamarm

Anaxandrides posted:

I would recommend using SSIS, but I don't know if you're on MSSQL. Script Task to automate and page scrape to a flat file, then a data flow from the flat file to your DB.

I'm using mySQL but if it makes this easier I am more than willing to virtualize a MSSQL setup and dump the database after all of the information has been inserted. "I know what some of these words mean" applies to your post in my mind, but I will be Googling my rear end off until I understand. I appreciate the direction pointing.

BlackMK4 fucked around with this message at 22:28 on Nov 20, 2014

# ? Nov 20, 2014 21:56

jdi448hsnvb: Dec 6, 2006; asfd

Using SQL-EX.com exercises to learn SQL and I'm currently stuck on exercise 51.

quote:

Find the names of the ships having the largest amount of guns among all the ships with the same displacement (taking into account Outcomes table).

Here is the full database explanation. Basically, each ship (ie each record in Ships table) is of a certain class, which connects it to the Class table. The Class table has the relevant columns of displacement and amount of guns (numGuns). The bit at the very end about the Outcomes table is just a complicating factor - some ships are not in the Ships table but show up in Outcomes table. However, I'm not worrying about this for now. I just want to get the main code working. Thing is, what I'm running throws an error:

code:

SELECT name FROM Ships s JOIN Classes c ON s.class = c.class
WHERE (c.numGuns, c.displacement) IN
(SELECT MAX(numGuns), displacement FROM Classes
GROUP BY Classes.displacement)

I'm getting: "An expression of non-boolean type specified in a context where a condition is expected, near ','." I'm guessing this is because the flavor of SQL the site runs doesn't allow using the double column thing I'm doing in between WHERE...IN. If so, I'm not sure of a different way to do this. Any help?

# ? Nov 22, 2014 02:08

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

That's kind of a weird question. The relationships are wackily denormalized (why have Class as a secondary key in Ships? Is ShipID and ClassID and Ships_Classes so hard?), the primary keys shouldn't be varchars, Battle changes to Name depending what table it's in.

EDIT-But yes, my gut feel is that the IN operator only accepts one argument. Maybe try EXISTS?

# ? Nov 22, 2014 03:14

Sedro: Dec 31, 2008

You should be able to convert any in-subquery to a join. Something like this

SQL code:

SELECT name
FROM Ships s
JOIN Classes c ON s.class = c.class
JOIN (
  SELECT MAX(numGuns) numGuns, displacement
  FROM Classes
  GROUP BY Classes.displacement
) t ON t.numGuns = c.numGuns AND t.displacement = c.displacement

# ? Nov 22, 2014 03:28

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

dammitcharlie posted:

code:
SELECT name FROM Ships s JOIN Classes c ON s.class = c.class
WHERE (c.numGuns, c.displacement) IN
(SELECT MAX(numGuns), displacement FROM Classes
GROUP BY Classes.displacement)
I'm getting: "An expression of non-boolean type specified in a context where a condition is expected, near ','." I'm guessing this is because the flavor of SQL the site runs doesn't allow using the double column thing I'm doing in between WHERE...IN. If so, I'm not sure of a different way to do this. Any help?

Yeah, IN only allows you to search for a single thing. I wouldn't use IN for this anyway -- the syntax here looks like it's aimed at MSSQL, right? You can do...

code:

;WITH cte(ShipName, NumberOfGuns, Displacement, Rank)
AS (SELECT name,
           numGuns,
           displacement,
           RANK() OVER(PARTITION BY displacement ORDER BY numguns)
           FROM Ships s
               JOIN Classes cl
                   ON s.class = cl.class)
SELECT s.ShipName,
       s.NumberOfGuns,
       s.Displacement
FROM cte c
WHERE c.Rank = 1

If you ask me, that's a bit more clear as to your intent. Plus I'm generally against windowed joins. They're generally more difficult to read, they cloud the intent of a join, and they are oftentimes performance hogs that are difficult to find. Personal call there.

# ? Nov 23, 2014 02:45

fluppet: Feb 10, 2009

If a MSP was taking a full backup of a SQL Server DB and then hourly diffs, why do i have a ~50GB transaction log.

I know why i have a transaction log that large but I'm trying to work out if the MSP have a reason for letting it get so huge beyond general incompetence?

# ? Nov 24, 2014 14:52

Anaxandrides: Jun 7, 2012; Every summer day in the desert is like a vacation in hell.

fluppet posted:

If a MSP was taking a full backup of a SQL Server DB and then hourly diffs, why do i have a ~50GB transaction log.

I know why i have a transaction log that large but I'm trying to work out if the MSP have a reason for letting it get so huge beyond general incompetence?

How many transaction/second is the DB doing? Is the log set to auto-shrink? When the initial transaction log backup was taken, how big was the log? How much unallocated space is shown in DBCC SQLPERF(LogSpace)?

Perhaps most importantly, is there any major nightly work done that would impact the log (index rebuilds, etc)? We have a 50GB transaction log on our main server. That is because of index rebuilds at night -- because we have enough free space available for the log to be this big, and since it happens every night, it's not worthwhile to shrink the unallocated space in the log. There's no performance penalty to a large log, but there is a (very minor) hit on the server every time that log has to grow beyond previously allocated space.

# ? Nov 24, 2014 19:06

jdi448hsnvb: Dec 6, 2006; asfd

Thanks goons.

# ? Nov 24, 2014 22:41

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Is there a way to put a note or a comment on a table in MSSQL 2008R2? I'm doing a bad thing (using NULL in a bit column when the flag can't be set for indeterminate reasons) and would like to leave a note or something for anyone who comes across it.

# ? Nov 25, 2014 20:17

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 15:25

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Scaramouche posted:

Is there a way to put a note or a comment on a table in MSSQL 2008R2? I'm doing a bad thing (using NULL in a bit column when the flag can't be set for indeterminate reasons) and would like to leave a note or something for anyone who comes across it.

You could try an extended attribute or something, but it would depend on someone looking for it. Why not leave comments in the code that performs the insert/updates?

# ? Nov 25, 2014 20:34

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

«‹›303 »