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 »

Aredna: Mar 17, 2007; Nap Ghost

Super Mario Shoeshine posted:

sorry for being a pest but i need some advice on how to separate a char field like "QUAKE\QUIT\JOHN" to 3 differents fields. (I am trying to separate the names field on a database by surnames and names.

What flavor of SQL are you using? Many support regular expressions which will make it easy and quick to sort out.

Is the delimiter actually a backslash or will it be something else (i.e. a space)?

# ? Jul 11, 2008 18:47

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 06:53

grimace: Jun 20, 2001; Jon Bence don't take
no phone surveys
because he's dead
RIP 19__ - 2009, 580 lbs

Super Mario Shoeshine posted:

sorry for being a pest but i need some advice on how to separate a char field like "QUAKE\QUIT\JOHN" to 3 differents fields. (I am trying to separate the names field on a database by surnames and names.

This is for MSSQL. I know how bad this is going to look. But unfortunately, there's no split string function in TSQL. I used a temp table and this appears to work. (no guarantees):

code:

select
charindex('\', names) as char1, 
charindex('\', names, charindex('\', names) + 1) as char2,
substring(names, 0, charindex('\', names)) as Field1,
-- substring(names, 0, char1)
substring(names, 
	charindex('\', names) + 1,
	charindex('\', names, charindex('\', names) + 1)
	- charindex('\', names) - 1
) as Field2,
-- substring(names, char2 + 1, len(names) - char2 - char1)
substring(names, 
	charindex('\', names, charindex('\', names) + 1) + 1, 
	len(names) - (charindex('\', names, charindex('\', names) + 1) - charindex('\', names))
) as Field3
from #splitthis

I'm leaving the char1 and char2 as reference - these would be the locations of the backslashes in the field. Then, selecting the three parts uses these locations for the lengths of each part.

# ? Jul 11, 2008 18:52

FoXyJess: May 21, 2005; by Ozmaugh

I have a feeling that there is a simple way to do what I need, but the answer is eluding me

.

Basically, I have a mySQL database with a birth date column (standard datetime field). There are a few thousand rows in my database. Most of the birth days I have in there are correct, but some are off by one hour, making the date incorrect.

For example, 2/19/1984 12:00:00 AM is a correct birthdate.

However, some birth dates are one hour behind, making the whole date wrong. Example: 12/31/1899 11:00:00 PM should be: 1/1/1900 12:00:00 AM.

Does anyone know of a way to add one hour to the dates that have an 11:00 PM date to correct them?

# ? Jul 12, 2008 04:54

Reaction Cat: Jun 24, 2008

This might do it:

code:

UPDATE whatever_table
	SET birthday = ADDTIME(birthday, '1:00:00')
	WHERE HOUR(birthday) = 23

If not that should at least get you going in the right direction..

# ? Jul 12, 2008 20:42

Zombywuf: Mar 29, 2008

chocojosh posted:

My learning of SSIS goes from "Hmm.. this tool is pretty neat and I love the concept but they need to get the interface improved and a few bugs fixed" down to "why the hell can't I do something so basic?".

In summary I've spent about 1.5 days just trying to answer the problem of "How can I send a nice error message to my business analyst/future developers so they know exactly what to fix". What a major pain in the rear end.

I did warn you.

# ? Jul 13, 2008 11:53

zim: Sep 28, 2001; price check on oink

I have no hair left to pull out...

I have a table called table...

code:

 Name | Color
-------------
David | red
  Jim | blue
David | red
  Jim | blue
 Lisa | blue
  Bob | green
  Bob | green
David | red
  Jim | green
 Lisa | blue
  Bob | blue
  Jim | red
 Lisa | blue

Name(s) make widgets of Color

I need to select Names of people who have only made one color of... widgets, or whatever.

code:

SELECT Name, count(distinct(Color)) FROM table group by Name

This gives me

code:

David | 1
 Lisa | 1
  Bob | 2
  Jim | 3

How would I structure a query that, in this scenario, would give me "David" and "Lisa", or anyone had only made one color of widgets ?

# ? Jul 14, 2008 19:50

chocojosh: Jun 9, 2007; D00D.

zim posted:

I have no hair left to pull out...

I have a table called table...

Do you expect us to do your homework?
http://www.w3schools.com/sql/sql_groupby.asp

# ? Jul 14, 2008 19:52

zim: Sep 28, 2001; price check on oink

its not my homework

but thank you, got it

# ? Jul 14, 2008 19:54

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

Aredna posted:

What flavor of SQL are you using? Many support regular expressions which will make it easy and quick to sort out.

Is the delimiter actually a backslash or will it be something else (i.e. a space)?

I am using MySQL and the delimiter is actually a backslash so its all crumped together and looks so terrible and oh god

# ? Jul 14, 2008 21:45

Aredna: Mar 17, 2007; Nap Ghost

Super Mario Shoeshine posted:

I am using MySQL and the delimiter is actually a backslash so its all crumped together and looks so terrible and oh god

MySQL supports regex, but from what I can find it does not support using regexs selecting a portion of a substring. If I'm wrong I'm sure someone will jump in here with an example

Otherwise, what grimace posted above will be the logic you'll want to use. The syntax will be slightly different for MySQL instead of MSSQL, but it should be close. Also, make sure you use two backslashes when you put them in the charindex so that they are escaped properly.

# ? Jul 14, 2008 22:40

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

Is a TRUNCATE, DROP faster than a DROP in SQL Server? The reason I'm guessing so is because with a drop, it would try to record everything in the transaction log. But if I truncate it first, there would be nothing to record, ergo, faster.

# ? Jul 15, 2008 18:57

Zombywuf: Mar 29, 2008

Triple Tech posted:

Is a TRUNCATE, DROP faster than a DROP in SQL Server? The reason I'm guessing so is because with a drop, it would try to record everything in the transaction log. But if I truncate it first, there would be nothing to record, ergo, faster.

Never dropped a large enough table to measure, but DROP DATABASE is near instantaneous, and I'd assume internally DROP TABLE uses similar, if not mostly the same, code path as TRUNCATE. So I can't imagine doing a TRUNCATE first would be any quicker.

# ? Jul 15, 2008 20:32

Stephen: Feb 6, 2004; Stoned

If I run

code:

SELECT COUNT(*) as i FROM table WHERE whatever=something;

and the results are empty, is there a way to have MySQL return i as 0 instead of an empty result set?

Edit: Nevermind, I'm a retard. I was including a "GROUP BY" in my original query that was preventing the return from being 0.

# ? Jul 15, 2008 20:48

DLCinferno: Feb 22, 2003; Happy

Zombywuf posted:

Never dropped a large enough table to measure, but DROP DATABASE is near instantaneous, and I'd assume internally DROP TABLE uses similar, if not mostly the same, code path as TRUNCATE. So I can't imagine doing a TRUNCATE first would be any quicker.

Doing a TRUNCATE then DROP should take slightly (microseconds) longer than just a DROP. They are both bulk operations that are not logging individual rows.

I've done them on large tables (500 million rows or more) so I know from experience.

# ? Jul 15, 2008 22:53

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

DLCinferno posted:

I've done them on large tables (500 million rows or more) so I know from experience.

Just curious, how many columns were in this table?

# ? Jul 15, 2008 23:12

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

What's the most efficient way to query a table for timestamps that are < 15 minutes old in MySQL?

code:

SELECT field FROM table WHERE (TIME_TO_SEC(TIMEDIFF(NOW(), timestamp))/60) < 15

or something like this

code:

SELECT field FROM table WHERE timestamp > UNIX_TIMESTAMP()-15*60

or something else?

# ? Jul 15, 2008 23:27

geetee: Feb 2, 2004; >;[

fletcher posted:

What's the most efficient way to query a table for timestamps that are < 15 minutes old in MySQL?
code:
SELECT field FROM table WHERE (TIME_TO_SEC(TIMEDIFF(NOW(), timestamp))/60) < 15
or something like this
code:
SELECT field FROM table WHERE timestamp > UNIX_TIMESTAMP()-15*60
or something else?

I am willing to bet that both queries get optimized down to the same thing.

# ? Jul 16, 2008 01:21

var1ety: Jul 26, 2004

fletcher posted:

What's the most efficient way to query a table for timestamps that are < 15 minutes old in MySQL?
code:
SELECT field FROM table WHERE (TIME_TO_SEC(TIMEDIFF(NOW(), timestamp))/60) < 15
or something like this
code:
SELECT field FROM table WHERE timestamp > UNIX_TIMESTAMP()-15*60
or something else?

In the first expression you are converting a date column from its native format into an integer for a comparison. Stick with the second expression. Although they are meant to be equivalent, the optimizer will more than likely not rewrite the first one to make them equivalent.

# ? Jul 16, 2008 03:04

MoNsTeR: Jun 29, 2002

Randomosity posted:

Alright, I'm sure this is really simple, but how can I select the rows from table A that do not have associated rows in table B?

We use this as an interview question. There are at least 5 available techniques, but somewhere between 50% and 75% of interviewees fail to provide even one working solution.

To give this post more content, the 5 I know of are:
- MINUS
- NOT IN
- NOT EXISTS
- NOT ANY (Oracle only? Bizarre syntax that I never use)
- null outer join

# ? Jul 16, 2008 03:46

chocojosh: Jun 9, 2007; D00D.

MoNsTeR posted:

We use this as an interview question. There are at least 5 available techniques, but somewhere between 50% and 75% of interviewees fail to provide even one working solution.

To give this post more content, the 5 I know of are:
- MINUS
- NOT IN
- NOT EXISTS
- NOT ANY (Oracle only? Bizarre syntax that I never use)
- null outer join

What is a NULL outer join? I thought it would be a "LEFT OUTER JOIN" (you could use Right outer join but in practice I've only seen left).

I do like the subquery answers -- a good trick would be to ask for TWO ways to do it :argh:

# ? Jul 16, 2008 04:51

Aredna: Mar 17, 2007; Nap Ghost

MoNsTeR posted:

We use this as an interview question. There are at least 5 available techniques, but somewhere between 50% and 75% of interviewees fail to provide even one working solution.

To give this post more content, the 5 I know of are:
- MINUS
- NOT IN
- NOT EXISTS
- NOT ANY (Oracle only? Bizarre syntax that I never use)
- null outer join

EXCEPT instead of MINUS on non-Oracle systems

For PostgreSQL (maybe others?) you can use: field <> ANY(...)

# ? Jul 16, 2008 05:22

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

chocojosh posted:

What is a NULL outer join? I thought it would be a "LEFT OUTER JOIN" (you could use Right outer join but in practice I've only seen left).

I do like the subquery answers -- a good trick would be to ask for TWO ways to do it

I think he means a LEFT OUTER JOIN with WHERE RightTable.key IS NULL

# ? Jul 16, 2008 15:22

DLCinferno: Feb 22, 2003; Happy

Triple Tech posted:

Just curious, how many columns were in this table?

Well, I just dropped a 953 million row table this morning. Here's the columns and size:

bigint 8
char 8
nvarchar 520
datetime 8
datetime 8
datetime 8
char 1
int 4

...table was about 230GB. Drop happened in less than a second, although now I need to shrink the database.

# ? Jul 16, 2008 16:35

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

Our instance of SQL Server 2005 (we are not technically adept/DB mastahs) had 130 columns over 4M rows, and it took over a minute to drop. So, I figured it was trying to log everything. The log is already set to simple.

# ? Jul 16, 2008 19:33

MoNsTeR: Jun 29, 2002

Jethro posted:

I think he means a LEFT OUTER JOIN with WHERE RightTable.key IS NULL

More or less. I would write it as:

code:

select a.*
  from table_a a
     , table_b b
 where a.key = b.key(+)
   and b.key is null

I find the JOIN keyword and all its SQL99 buddies completely and utterly unreadable, and I think they make learning SQL much, much more difficult than it needs to be.

# ? Jul 16, 2008 20:06

DLCinferno: Feb 22, 2003; Happy

Triple Tech posted:

Our instance of SQL Server 2005 (we are not technically adept/DB mastahs) had 130 columns over 4M rows, and it took over a minute to drop. So, I figured it was trying to log everything. The log is already set to simple.

Is autoshrink on? You should turn it off on production servers. It may be trying to reclaim that space.

# ? Jul 16, 2008 20:18

var1ety: Jul 26, 2004

MoNsTeR posted:

More or less. I would write it as:
code:
select a.*
  from table_a a
     , table_b b
 where a.key = b.key(+)
   and b.key is null
I find the JOIN keyword and all its SQL99 buddies completely and utterly unreadable, and I think they make learning SQL much, much more difficult than it needs to be.

I'll line up on the other side. ANSI joins are the only thing that make large statements understandable and readable. The (+) operator makes life miserable, has limitations, and even Oracle recommends you get on with your life.

10.2 SQL Reference posted:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

# ? Jul 16, 2008 20:20

Squashy Nipples: Aug 18, 2007

var1ety posted:

I'll line up on the other side. ANSI joins are the only thing that make large statements understandable and readable. The (+) operator makes life miserable, has limitations, and even Oracle recommends you get on with your life.

I agree, I kinda like using JOIN. If I'm doing simple inner join, then I just set the relevant fields equal in the WHERE clause, but if I need anything weird and/or complicated, I'll use the full JOIN notation every time.

Still, thats a pretty cool interview question.

# ? Jul 16, 2008 21:13

No Safe Word: Feb 26, 2005

Triple Tech posted:

Our instance of SQL Server 2005 (we are not technically adept/DB mastahs) had 130 columns over 4M rows, and it took over a minute to drop. So, I figured it was trying to log everything. The log is already set to simple.

130 columns :wth:

# ? Jul 16, 2008 21:41

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

Yeah, obviously if I was at the helm, there would be a lot more judicious use of what goes where, etc. But they're sort of playing a shotgun approach to the way we're modeling the data, so it just so happens that one slice of our in-house model has 130 possible dimensions. The vast majority of the fields are null, and I can scream audit until the cows come up, but that's just the way it is.

The data that we're getting from other people though, 80 columns, easy. And we're still talking a few million rows, per month.

# ? Jul 16, 2008 22:12

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

I just found a way to separate that string but i need to know how to pass the result of a string function to a temp variable, i mean for example if i use this function SELECT RIGHT() i get as a result a number, is there a way to pass this number to a variable i just defined in the session?

# ? Jul 18, 2008 17:50

Ardhanari: Mar 23, 2006

Super Mario Shoeshine posted:

I just found a way to separate that string but i need to know how to pass the result of a string function to a temp variable, i mean for example if i use this function SELECT RIGHT() i get as a result a number, is there a way to pass this number to a variable i just defined in the session?

SELECT @variable = RIGHT(whatever you're doing here)

# ? Jul 18, 2008 18:34

Pussy v. Sperguson: Apr 28, 2003

Do you guys have an efficient method of handling the averaging of user-submitted numerical ratings? Say I have a site with items you can rate between 1 and 10, where I want to display the aggregate average of all votes. I was thinking of having a temporary table that gets an additional row added each time a user creates a rating, and a stored procedure that gets called every hour or so to add the temp values to a permanent table, after which it flushes out the temporary one.

Would this make more sense than just having a single table with columns for 'count of reviews' and 'total of review values', and updating these values directly each time a user submits a review?

# ? Jul 18, 2008 22:17

Zombywuf: Mar 29, 2008

chemicalhero posted:

Do you guys have an efficient method of handling the averaging of user-submitted numerical ratings?

A table that stores a total rating and total number of users that have rated it? You'd only need to access one row for writing ratings and retrieving them.

# ? Jul 18, 2008 23:48

craisins: May 17, 2004; A DRIIIIIIIIIIIIVE!

I have a FoxPro table (close enough to SQL?) that has a few rows that are flagged as deleted. Is there any way (through code) that I can either unset these deleted flags or that I can still access the data?

I know by typing SET DELETED OFF and BROWSE, I can view the data in the table...but I need to be able to access deleted data from a program that I'm running.

Please say there's some way to do this

EDIT: I think I found a way

SET DELETED OFF
SELECT * FROM table WHERE DELETED()=.T.

craisins fucked around with this message at 15:52 on Jul 21, 2008

# ? Jul 21, 2008 15:40

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!

I have a SQLite database that I'm trying to search through, and firstly LIKE doesn't use indexing, so I'm having some really slow results.

So I'm trying to move to using =>, as I'm doing a search where I just want all character that are bigger than the first part of the string - if I type Al I want it to list Alabama, and for strings in general, it seems to be using the index with =>.

However, there are also some things I'd like to search that are numeric columns that it treats strictly as numeric columns. So if I search for "5" I'll get the results I want "50, 51, etc" but I'll also get 60, which isn't what I want.

My query started simple, and has evolved to this mess which still doesn't do what I want:

SELECT Report.NumColumn FROM Report WHERE +CAST(Report.NumColumn AS text) >=CAST(5 AS text)

Anyone ever had to do something like this before?

# ? Jul 21, 2008 18:02

var1ety: Jul 26, 2004

Alex The Great posted:

I have a SQLite database that I'm trying to search through, and firstly LIKE doesn't use indexing, so I'm having some really slow results.

So I'm trying to move to using =>, as I'm doing a search where I just want all character that are bigger than the first part of the string - if I type Al I want it to list Alabama, and for strings in general, it seems to be using the index with =>.

However, there are also some things I'd like to search that are numeric columns that it treats strictly as numeric columns. So if I search for "5" I'll get the results I want "50, 51, etc" but I'll also get 60, which isn't what I want.

My query started simple, and has evolved to this mess which still doesn't do what I want:

SELECT Report.NumColumn FROM Report WHERE +CAST(Report.NumColumn AS text) >=CAST(5 AS text)

Anyone ever had to do something like this before?

If I understand you correctly you could solve your problem by also adding a substr() predicate. Something like this:

code:

substr(col, 1, length('Al')) = 'A1'

Edit:

A poster named D. Richard Hipp posted some advice for a similar question at http://www.nabble.com/SQLite-Like-Query-Optimization-td15511886.html.

D. Richard Hipp posted:

LIKE operators cannot use indices unless the index is
case insensitive. Use GLOB for case sensitive fields.

LIKE and GLOB operators cannot use indices if the pattern
begins with a wildcard.

Nothing in SQLite will use an index if you are connecting
terms using OR.

It looks like what you really want to use here is a full-text
index. Please read about the FTS3 support in SQLite. That
seems to be what you are trying to accomplish.

var1ety fucked around with this message at 20:43 on Jul 21, 2008

# ? Jul 21, 2008 18:23

Mr. DNA: Aug 9, 2004; Megatronics?

I'm running an analysis on data being generated by a PLC. The database is formatted specially for some software that runs on site so it's a bit of a pain to get all the data I need. Here's the query I'm running:

code:

I removed the code because it was breaking the tables and my question was answered.

ADJUSTEDORIGINATIONTIME is created by the DATEADD routine to compensate for timezone and DST. I want to filter results by this adjusted time but when I try to run this query there's an error saying "Invalid column name 'AdjustedOriginationTime'". I can copy the DATEADD portion to the filter section of the query and it runs as expected, but it's silly to do the same calculation three times.

It seems the solution should be very simple, but I'm quite new to this. Help would be much appreciated.

Mr. DNA fucked around with this message at 20:10 on Jul 22, 2008

# ? Jul 21, 2008 18:26

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

Mr. DNA posted:

I'm running an analysis on data being generated by a PLC. The database is formatted specially for some software that runs on site so it's a bit of a pain to get all the data I need. Here's the query I'm running:

ADJUSTEDORIGINATIONTIME is created by the DATEADD routine to compensate for timezone and DST. I want to filter results by this adjusted time but when I try to run this query there's an error saying "Invalid column name 'AdjustedOriginationTime'". I can copy the DATEADD portion to the filter section of the query and it runs as expected, but it's silly to do the same calculation three times.

It seems the solution should be very simple, but I'm quite new to this. Help would be much appreciated.

What RDBMS? Looks like either MSSQL or MySQL, but I don't know which.

Either way, the short answer is "it may be silly, but that's pretty much the way to do it." The long answer is "stick it in a view or (RDBMS depending) a CTE or subquery."

# ? Jul 21, 2008 21:43

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 06:53

Mr. DNA: Aug 9, 2004; Megatronics?

Jethro posted:

What RDBMS? Looks like either MSSQL or MySQL, but I don't know which.

Either way, the short answer is "it may be silly, but that's pretty much the way to do it." The long answer is "stick it in a view or (RDBMS depending) a CTE or subquery."

It's MSSQL. Thanks for your answer. Those key words look like they'll be enough for me to solve the problem with some reading. Thanks again for your help!

# ? Jul 22, 2008 00:12

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

«‹›303 »