Re: [SQL] SQL problem: bank account
How about: select max(transaction_id) from bank_account group by customer_id Should work... Detlef -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von Erik G. Burrows Gesendet: Montag, 2. Juni 2003 07:30 An: [EMAIL PROTECTED] Betreff: [SQL] SQL problem: bank account It seems to me this is a simple problem, but the solution eludes me. I have a table: bank_account ( transaction_id int not null serial, customer_id int not null references customer(id), ts timestamp not null default now(), amount float not null, balance float not null, primary key(transaction_id) ) I need to get the most recent transaction for each customer. I need only the transaction ID, but the entire row would be best. I have two solutions, both of which are too slow for use in my interactive web-based interface: Solution1: Outer left self join: SELECT ba1.* FROM bank_account ba1 LEFT OUTER JOIN bank_account ba2 ON ba1.customer_id = ba2.customer_id AND ba1.ts < ba2.ts WHERE ba2.ts IS NULL; This query works great on tables of less than a few thousand rows. For my 300k row table, it takes several hours. Solution2: max-concat trick SELECT split_part(max( extract(EPOCH from ts)::VARCHAR || '' || transaction_id::VARCHAR), '', 2)::INT FROM bank_account GROUP BY customer_id This is an ugly and obviously inefficient solution, but it does the job in about 1/2 hour. Still too long though. I've been working on this problem for days, and consulting friends. No elegant, fast solution is presenting itself. As I said, I feel I'm not seeing the obvious solution in front of my face. In the mean-time I can use this query to do the job on a per-customer basis: select * from bank_account where id = and ts = (select max(ts) from bank_account ba2 where ba2.customer_id = bank_account.customer_id); However, doing this for all 40,000 customers is not workable as a manual process. My last resort is to do it this way to pre-generate the report, but I'd far rather do it real-time. Help! My brain hurts! -- Erik G. Burrows - KG6HEA www.erikburrows.com PGP Key: http://www.erikburrows.com/[EMAIL PROTECTED] ---(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 ---(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] Merge Record in Database(SQL Statement)
Not quite sure what the question is Assuming, you would like to normalize the data, I suggest to create 4 tables: create table gpa (id integer primary key,gpa varchar(32) not null unique); create table major (id integer primary key,major varchar(32) not null unique); create table birthplace (id integer primary key,birthplace varchar(32) not null unique); create table xxx ( id integer primary key, attributename varchar(64) not null, -- this might be unique too id_major integer references major(id), id_birthplace integer references birthplace(id), id_gpa integer references gpa(id) ); insert into gpa (id,gpa) values (1,'excellent'); insert into major (id,major) values (1,'science'); insert into birthplace (id,birthplace) 1,'India'); insert into xxx (id,attributename,id_major,id_birthplace,id_gpa) values (1,'Record 1',1,1,1); insert into xxx (id,attributename,id_major,id_birthplace,id_gpa) values (1,'Record 2',1,1,1); Now selecting the records would be something like: select x.attributename,m.major,b.birthplace,g.gpa from attributename a,major m,birthplace b,gpa g where x.id_major=m.id and x.id_birthplace=b.id and x.id_gpa=g.id Hope that helps Detlef -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von Shahbuddin Md Isa Gesendet: Montag, 7. Juli 2003 03:24 An: [EMAIL PROTECTED] Betreff: [SQL] Merge Record in Database(SQL Statement) Hai.. How to merge record in database(sql statement) if record same attribute, examples:- Attribute Major Birth_Place GPA Record 1science India excellent Record 2science India excellent Please help me.. Powered by Fastmail from http://www.i-fastmail.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Datatype conversion help
I tried select to_date(substring(to_char(yr,'0009'),2,4)||substring(to_char(mn,'09'),2,2)|| substring(to_char(dy,'09'),2,4),'MMDD'); which works fine Detlef -Ursprungliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von Dmitry Tkach Gesendet: Donnerstag, 10. Juli 2003 00:52 An: Yasir Malik Cc: [EMAIL PROTECTED] Betreff: Re: [SQL] Datatype conversion help Yasir Malik wrote: >I used trim and here's what I came up with: >to_date(trim(to_char(yr, '') || trim(to_char(mn, '00')) || >trim(to_char(dy, '00'))), 'MMDD') > >Apparently to_char adds a space to the charecter you are casting. > > I know :-) And lpad doesn't - that's why I suggested it :-) Dima > >On Wed, 9 Jul 2003, Dmitry Tkach wrote: > > > >>Date: Wed, 09 Jul 2003 18:40:37 -0400 >>From: Dmitry Tkach <[EMAIL PROTECTED]> >>To: Yasir Malik <[EMAIL PROTECTED]> >>Cc: [EMAIL PROTECTED] >>Subject: Re: [SQL] Datatype conversion help >> >>What about lpad? >> >>select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003'; >> ?column? >> >> 07-09-2003 >>(1 row) >> >> >>I hope, it helps... >> >>Dima >> >>Yasir Malik wrote: >> >> >> >>>Thank you so much! But my problem is that when I do >>>to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr, >>>'') >>> >>>where mn, dy, and yr are ints, is that the output has a space after the >>>the dash. For example, I get >>>07- 25- 1994 >>> >>>instead of what I want: >>>07-25-1994 >>> >>>Thanks, >>>Yasir >>> >>>On Tue, 8 Jul 2003, Richard Rowell wrote: >>> >>> >>> >>> >>> Date: 08 Jul 2003 15:21:33 -0500 From: Richard Rowell <[EMAIL PROTECTED]> To: Yasir Malik <[EMAIL PROTECTED]> Subject: Re: [SQL] Datatype conversion help On Tue, 2003-07-08 at 15:07, Yasir Malik wrote: >I've tried to_char(in_val, '99'), and that returns a string that is two > > > > select to_char(9,'00'); >>>---(end of broadcast)--- >>>TIP 8: explain analyze is your friend >>> >>> >>> >>> >> >> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
