SQL

the above isn't, unfortunately, full text search so i can't just drop things (although AND and THE are dropped). it's artist names, release names or track titles. english and swedish (although we do nothing with the swedish equivalents of THE or AND (which are different depending on gender and plurality = can of worms))

i like sql but, yes, it is a bit of a black art getting what you want out of it. i hate the bits that all dbs add to it to do the non-standard stuff like sequences, oracle being the worst. (dba is a slightly different job from db programmer though, much the same way the bloke who looks after the linux machines here couldn't write 'hello world' (ok, maybe in bash))

SQL pwned me yesterday when I wanted to extract samples taken at 6:00:00, 6:10:00, 6:20:00 from a dataset that had samples logged every second, starting at 5:55:15. I'm sure it can be done, just wasn't smart enough to get it sorted in the timeframe when I needed it.

where to_char(date, 'HH24:mm:ss') like '06:_0:00'

?

Koogs! Type safety!

I mean that would work, but in principle at least, it's scarily unsafe! Plus it's a tablespace scan.

quick and dirty, my middle name. s.

Should have been more explicit - it's Access, so no to_char. I thought it could be as simple as WHERE time LIKE '__:_0:00 __', but no. Not even converting the field to text first (from Date/Time) let that work. I was also thinking I could convert the field to a pure number (thinking I'd get the UCT and could work some math magic on it), but that throws errors.

I would read the primary key and the time in, get them all and sort them out in program code, rather than trying to do it in SQL. If the table's too big, then you could write a loop and get every x minutes' worth of data and pick out the rows you want.

Once you've got them, execute a big IN (...) query to get the rows you want.

You could avoid that step if you've enough memory/time to get the whole rows at a time, I guess.

You're right, it's a messy problem!

i guess my solution is a function of a) being self taught (and with an idiot for a teacher) and b) using vi or grep for editing / sorting needs and, consequently, seeing everything as a string.

getting away from Jaq's specific problem for the minute:

would it be better if you cut down the result space first, like (pseudocode alert!)

where time.hour = 6 and time.seconds = 00 and to_char(time, 'mm') like '_0'

(in the same way "make && make install" or "if (str == null || str.trim().length() == 0)" (ie there's an implicit AND or OR in there and the second part only executes if the first part passes / fails) or is what gets executed a whim of the oracle developers?

or would i be better off going the whole hog and doing it all as numbers:

where time.hour = 6 and time.seconds = 00 and (time.minutes % 10) = 0

returning to Jaq's problem, DatePart()?

http://office.microsoft.com/en-us/access/HP010984871033.aspx

I could see that working, using DatePart() to return the minute and second. WHERE (((DatePart("n",FI15B_TI15D.TIME)) Like '_0')) AND (((DatePart("s",FI15B_TI15D.TIME)) = '00')) doesn't throw errors but also doesn't return anything. Running it with just the seconds clause or just the minutes clause also don't return anything. I'll keep poking at it though.

This is a dataset extracted from our running system as .csv files, btw. About 35k records in a purpose-built .mdb so I can hack away at it w/ impunity.

There were hard brackets around the tablenames, btw, but the BBCode parser got all choked up on them.

put the datepart()s in the select part and take them out of the where part and see what they are returning, probably isn't what you expect.

True. I bailed on it though and wrote up a function in my favorite environment (which handles .dbfs natively, and very nicely) instead.

ha ha. just been sent some optimised versions of the queries i'd written:

he optimised out an entire join against the 'artist' table. which was nice of him. except the query was meant to be returning a list of artist objects to fill up the Artist ejb things.

(he also removed a left join which meant we had to populate the previously left joined table in order to get the required rows returned - 413,000 mostly empty rows. per service (of which we have 4 and counting.)

Disheartening: http://www.sdtimes.com/printArticle/LatestNews-20070715-44.html

How weird.. Eveything I've ever seen in the past 10 years or so has suggested Oracle #1 and DB2 #2... Not that that's anything but disheartening too.

I'm wondering how the question was framed. Also, not even 700 people asked = this is a very very very small sample.

Thing is, my enterprise uses it, but the strategy is DB2 and Oracle; SQL Server is tolerated for packages, but not in-house applications. By that logic, we would have voted yes, to this study, when in fact, it's accounts for about 0.5% of usage as compared with DB2.

http://blog.labnotes.org/2007/09/02/couchdb-thinking-beyond-the-rdbms/

oh shit son

JSONned