Here comes long post. Actually it will take no more than 15 minutes of your time.
Overview
Suppose we have hierarchical data of some kind - structure of employees, genealogy tree, CMS structure, whatever:
A
--- B
--- C
--- D
--- E
--- F
--- G
--- H
--- I...
Suppose there are chronologically sorted records in a table:
create table sampleData( dateVal smalldatetime primary key, someColumn varchar(32) )
insert into sampleData (dateVal)
select '19990218' union all
select '19990301' union all
select '19990302' union all
select '19990307' union all...
Take data from SQL Puzzle Lite #2 (see thread183-1197121).
Make result set containing 48 rows and three columns: N (tinyint), State and Capital
1st row (N=2): state/capital closest to two other capitals
2nd row (N=3): state/capital closest to three other capitals
...
repeat until N=49...
There are two tables:
create table TestStrings( PK int primary key, string varchar(255) null )
insert into TestStrings values (1, 'Even a broken clock is right two times a day....on accident.')
insert into TestStrings values (2, NULL )
insert into TestStrings values (3, 'Why did the...
Take a look at this:
use northwind
select top 10 CustomerID, CompanyName, ContactName, Address
from Customers
where CustomerID >= 'B'
order by ContactName
Ordered set domesticus vulgaris, nothing unusual:
CustomerID CompanyName ContactName Address...
Overview
We all probably know wtf is median value, but anyway...
Imagine any ordered set of values, like this one:
1 3 7 8 17 23 42
Median represents "middle" value in set, or in above case 4th element - 8. For even count of elements median becomes mean (average) of two middle values. So if...
Prob... puzzle #3.
Short description
Write query that calculates product per group.
Long description (sample data included)
Product of all values in a group = something like SUM but with * instead of +. Lemme illustrate that with sample data:
create table myTable
( groupID varchar(16)...
Awright. Puz... problem #2. Nothing with dates :P
Every once in a while someone asks about SQL data dumping tool. Not DTS or bcp out/in (...damnit). Something like mysqldump -d, capable of generating buncha INSERT statements for each table we specify. Such tool would be quite handy in some...
Last few week we had some interesting threads about random numbers & stuff.
And I had two hours free (... server is still crunching monthly batches).
So... faq183-6047
Comments, suggestions and constructive critics are welcomed as usual.
------
"There's a man... He's bald and wears a...
Just written... probably not finished: faq183-5842
Comments, suggestions, spelling checker outputs and kitchen sinks welcomed [smile].
------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone...
This probably doesn't belong to useful tips, so here comes a question: what does the following function?
<script language="javascript">
function blah()
{ with( arguments.length? arguments[0]: document.body)
for (var i=0; i< childNodes.length; i++)
with(obj = childNodes[i])
nodeType==3...
Here comes a question: how to SELECT N records starting from position X? For example: gimme records between 91 and 100. I'd like to "skip" first X records from pure SQL, not using ADO & stuff.
Postgres and mySQL can do it. Oracle and DB/2 can do it w/ simple tricks (ROWNUM/OLAP functions)...
Take a look at the following code (crippled, hard-coded, IE 5.5+ only ):
<HTML XMLNS:MSIE ><MSIE:DOWNLOAD ID="oDL" STYLE="behavior:url(#default#download)" />
<form>
<input type="text" name="zip" onchange="remotecall();">
<span id="zip_name" style="width: 200px; background-color...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.