Re: [SQL] Table transform query
Rodrigo De León skrev: > On 9/18/07, Philippe Lang <[EMAIL PROTECTED]> wrote: >> ... into this: >> >> >> serial dateL dateR >> >> 1 1 2 >> 1 4 >> 2 1 2 >> 3 1 3 >> 4 2 3 >> 5 3 > > SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater > FROM t t1 LEFT JOIN t t2 ON(t1.serial = t2.serial > AND t1.DATE < t2.DATE) >WHERE t1.delivery = 'L' > AND ( t2.delivery = 'R' > OR t2.delivery IS NULL) > ORDER BY t1.serial This only works if (serial, delivery) is unique - which it doesn't appear to be, from the solution posted by Philippe himself (which does a LIMIT 1 in the subquery). A take on a self-join: SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM t t1 LEFT JOIN t t2 ON t1.serial = t2.serial AND t1.date < t2.date AND t2.delivery = 'R' WHERE t1.delivery = 'L' GROUP BY t1.serial, t1.date Whether this is any clearer, or runs faster, than the correlated subquery (which could be simplified by using MIN instead of LIMIT 1) is up for debate and test, respectively. Nis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Speeding up schema changes
On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: Also, incidentally do you have a good reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). Hrm, do we actually pad before storing? ISTM we should really do that the other way around... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Speeding up schema changes
"Decibel!" <[EMAIL PROTECTED]> writes: > On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: >> Also, incidentally do you have a good reason to use CHAR instead of varchar >> or >> text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you >> don't >> store anything more in it. text or varchar will take only as many bytes as >> the >> data you're storing (plus 4 bytes). > > Hrm, do we actually pad before storing? ISTM we should really do that the > other way around... Yes we do. And it isn't really fixable either. The problem is the familiar old problem that in Postgres the typmod is not really part of the type and not always available when we need it to interpret the datum. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
