[SQL] fumbling for join syntax
hi- i have two tables which are used to track questions and answers in a trivia game. the structure of the tables is as follows: CREATE TABLE triv_q_r ( id VARCHAR(10) CONSTRAINT triv_q_r_pkey PRIMARY KEY, t_idVARCHAR(10) CONSTRAINT triv_q_r_t_id_fkey NOT NULL REFERENCES triv_r(id) ON DELETE CASCADE, questionVARCHAR(1024) NOT NULL, num INTEGER NOT NULL, c1 VARCHAR(1024) NOT NULL, c2 VARCHAR(1024) NOT NULL, c3 VARCHAR(1024), c4 VARCHAR(1024), c5 VARCHAR(1024), ans INTEGER NOT NULL, exp VARCHAR(1024), tm INTEGER NOT NULL DEFAULT 1 ); CREATE TABLE triv_a_r ( tq_id VARCHAR(10) CONSTRAINT triv_a_r_tq_id_fkey NOT NULL REFERENCES triv_q_r(id) ON DELETE CASCADE, fid_c VARCHAR(10) NOT NULL, login VARCHAR(20) NOT NULL, ans INTEGER NOT NULL DEFAULT 0, stime TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP, etime TIMESTAMP(2), tm INTEGER NOT NULL DEFAULT 0 ); right now, i'm givng a scoring overview using the following query: select a.login as user, count(a.login) as score, sum(a.tm)/1000 as time from triv_a_r a, triv_q_r b where a.tq_id = b.id and a.ans = b.ans group by a.login order by score desc, time asc; triv=> \i scoring.sql user | score | time --+---+-- jobtester| 3 |9 paul | 2 |6 marcyun | 1 |2 paulie | 1 |2 winstonchurchill | 1 |2 djk121 | 1 |3 this works fine, but it's been requested that the scoring query also include scores for those who answered incorrectly. i thought that i might be able to do this with a left outer join: select triv_a_r.login as user, count(triv_a_r.login) as score, sum(triv_a_r.tm)/1000 as time from triv_a_r LEFT OUTER JOIN triv_q_r ON (triv_a_r.tq_id = triv_q_r.id) where triv_a_r.ans = triv_q_r.ans group by triv_a_r.login order by score desc, time asc; but this gives me exactly the same results as before, because i still need where triv_a_r.ans = triv_q_r.ans to determine if a question was answered correctly. in short, i'd like to have a single query that extracts the following from these two tables: number attempted, number correct, score, total time, login any pointers woudl be greatly appreciated. thanks- dan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] fumbling for join syntax
Daniel, > select triv_a_r.login as user, count(triv_a_r.login) as score, > sum(triv_a_r.tm)/1000 as time > from triv_a_r LEFT OUTER JOIN triv_q_r ON (triv_a_r.tq_id = > triv_q_r.id) > where triv_a_r.ans = triv_q_r.ans > group by triv_a_r.login > order by score desc, time asc; A little SQL trick: select triv_a_r.login as user, SUM(CASE WHEN triv_a_r.ans = triv_q_r.ans THEN 1 ELSE 0 END) as score, sum(triv_a_r.tm)/1000 as time from triv_a_r LEFT OUTER JOIN triv_q_r ON (triv_a_r.tq_id = triv_q_r.id) group by triv_a_r.login order by score desc, time asc; Josh Berkus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] new calculated column
Hi; I've got a table with two fields and about 3000 rows, the second one is a character field, what can have about twenty different values; of course these values are repeated a lot of times in the table. I need to create a new column of type integer, whose value depens on the character fields. The values of the new column are not important, the important thing is who can I create this column and assign a different integer to a different char value in the other column. Thanks -- Javier --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Update help
update table1 set price = table2.price where table1.productId = table2.productId; (Of course, both tables must have a different name). :) Ligia ""[EMAIL PROTECTED]"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > This is a multi-part message in MIME format. > > --=_NextPart_000_00AA_01C2533B.BE837FA0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hi, > Let say I have 2 Product table, both of them has columns ProductID and Price > What is the update command if I want to update all Prices of first table to= > be equal with Price in second table? > > Please Help. > > Yudie > --=_NextPart_000_00AA_01C2533B.BE837FA0 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > > > > > > > > Hi, > Let say I have 2 Product table, both of th= > em has=20 > columns ProductID and Price > What is the update command if I want to up= > date=20 > all Prices of first table to be equal with Price in second=20 > table? > > Please Help. > > Yudie > > --=_NextPart_000_00AA_01C2533B.BE837FA0-- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] new calculated column
HI. simple (tedious) solution would be do fire 20 sqls. update tabel set f1= where f2='some varchar1'; update tabel set f1= where f2='some varchar2'; update tabel set f1= where f2='some varchar3'; incase there is some the integer is a function of varchar u may translate to the corresponding SQL. regds mallah. > Hi; > > I've got a table with two fields and about 3000 rows, the second one is a character >field, what > can have about twenty different values; of course these values are repeated a lot of >times in > the table. I need to create a new column of type integer, whose value depens on the >character > fields. The values of the new column are not important, the important thing is who >can I create > this column and assign a different integer to a different char value in the other >column. > Thanks > -- > Javier > > --- > > ---(end of broadcast)--- TIP 4: >Don't 'kill -9' > the postmaster - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Hardware performance for large updates
Folks, I'm doing some massive data transformations on Postgresql, and they're a lot slower than they should be. I'm looking for some tips on improving things. If the PGSQL-PERFORMANCE list was ever created, please tell me and I'll go over there. The update: A series of 7 update statements which cull data from a 1.5 million row table to update a 120,000 row table. The Machine: A dual-processor RAID 5 UW SCSI server. The postgresql.conf settings: Connections: 128 Shared Buffers: 256 Sort Mem: 1024 Checkpoint Segments: 16 Stats on. Light debug logging. The problem: The update series (done as a function) takes 10-15 minutes. During this time, the CPU is never more than 31% busy, only 256mb of 512 is in use, and the disk channel is only 25% - 50% saturated.As such, is seems like we could run things faster. What does everybody suggest tweaking? -Josh Berkus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Hardware performance for large updates
Josh Berkus wrote: > The problem: The update series (done as a function) takes 10-15 > minutes. During this time, the CPU is never more than 31% busy, only > 256mb of 512 is in use, and the disk channel is only 25% - 50% > saturated.As such, is seems like we could run things faster. > > What does everybody suggest tweaking? > I think we'd need more information to be of any help -- schema, functions, explain output, etc. I do think you probably could increase Shared Buffers, as 256 is pretty small. There's been a lot of debate over the best setting. The usual guidance is start at 25% of physical RAM (16384 == 128MB if you have 512MB RAM), then tweak to optimize performance for your application and hardware. You might also bump sort mem up a bit (maybe to 2048). Again, I would test using my app and hardware to get the best value. Are you on a Linux server -- if so I found that fdatasync works better than (the default) fsync for wal_sync_method. HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
