[SQL] Time Results Conversion
Hello all, I have now upgraded to 7.0.2 and am very pleased with it. I do have a question about a result that I am getting. When I sum an interval field will get "1 01:01:01" representing "25 hours 1 minute 1 second" The result that I need is just the sum of the hours minutes and seconds and No days ie roll the day back into the hour field. Is it possible for me to do this and if so How?
[SQL] Select subset of rows
Hi folks, I have this table CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY NUMERIC(5,2)); I want to select only the employees' names who have the 5 highest salaries. Paulo Siqueira
Re: [SQL] Select subset of rows
Of course immediately after sending the last message and logging off my ISP I figured out the simpler way for the third one: begin; select salary into temp saltemp from employee order by salary desc limit 5; select name from employee where exists (select * from saltemp where saltemp.salary=employee.salary); end; Stephan Szabo [EMAIL PROTECTED] On Sun, 27 Aug 2000, Paulo Roberto Siqueira wrote: > Hi folks, > > I have this table > > CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY > NUMERIC(5,2)); > > I want to select only the employees' names who have the 5 highest salaries. > > > > > Paulo Siqueira >
Re: [SQL] Select subset of rows
On Sun, 27 Aug 2000, Paulo Roberto Siqueira wrote: > Hi folks, > > I have this table > > CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY > NUMERIC(5,2)); > > I want to select only the employees' names who have the 5 highest salaries. Well, that depends on what you want to do in case of duplicate salaries. If you don't care about duplicate salaries, and you don't mind the possibility of non-deterministic responses in some cases: select name from employee order by salary desc limit 5; If you want to take the 5 highest salary values and find however many people have those salaries, I think this works: begin; select distinct salary into temp saltemp from employee order by salary desc limit 5; select name from employee where exists (select * from saltemp where saltemp.salary=employee.salary); end; If you want to get at least 5 people but don't want to cut off at 5 if the 5th, 6th, etc people are tied, I'm sure there's a better way, and I probably made at least one mistake in writing this out, but... begin; select distinct salary into temp saltemp from employee order by salary desc limit 5; select salary, count(*) into temp saltemp2 from employee where exists(select * from saltemp where saltemp.salary= employee.salary) group by salary; select saltemp2.salary, sum(case when saltemp2_2.salary<= saltemp2.salary then 0 else saltemp2_2.count end) into temp saltemp3 from saltemp2,saltemp2 saltemp2_2 group by saltemp2.salary; select name from employee,saltemp3 where employee.salary= saltemp3.salary and saltemp3.sum<5 order by employee.salary desc; end;
Re: [SQL] queries and inserts
Removing indexes will speed up the INSERT portion but slow down the SELECT portion. Just an FYI, you can INSERT into table (select whatever from another table) -- you could probably do what you need in a single query (but would also probably still have the speed problem). Have you EXPLAINed the SELECT query to see if index scans are being used where possible? -Mitch - Original Message - From: "Rini Dutta" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 25, 2000 12:20 PM Subject: [SQL] queries and inserts > Hi, > > I am interested in how to speed up storage. About 1000 > or more inserts may need to be performed at a time , > and before each insert I need to look up its key from > the reference table. So each insert is actually a > query followed by an insert. > > The tables concerned are : > CREATE TABLE referencetable(idx serial, rcol1 int4 NOT > NULL, rcol2 int4 NOT NULL, rcol3 varchar(20) NOT > NULL, rcol4 varchar(20), PRIMARY KEY(idx) ... > CREATE INDEX index_referencetable on > referencetable(rcol1, rcol2, rcol3, rcol4); > > CREATE TABLE datatable ( ref_idx int4, > start_date_offset int4 NOT NULL, stop_date_offset int4 > NOT NULL, dcol4 float NOT NULL, dcol5 float NOT NULL, > PRIMARY KEY(ref_idx, start_date_offset), CONSTRAINT c1 > FOREIGN KEY(ref_idx) REFERENCES referencetable(idx) ); > > I need to do the following sequence n number of times > - > 1. select idx (as key) from referencetable where > col1=c1 and col2=c2 and col3=c3 and col4=c4; (Would an > initial 'select into temptable' help here since for a > large number of these queries 'c1' and 'c2' > comnbinations would remain constant ?) > 2. insert into datatable values(key, ); > > I am using JDBC interface of postgresql-7.0.2 on > Linux. 'referencetable' has about 1000 records, it can > keep growing. 'datatable' has about 3 million records, > it would grow at a very fast rate. Storing 2000 > records takes around 75 seconds after I vacuum > analyze. (before that it took around 40 seconds - ???) > . I am performing all the inserts ( including the > lookup) as one transaction. > > Thanks, > Rini > > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ >
[SQL] Complex query
Hey everybody !!! I am new on this list !!! I have a little problem . I try this on my system: "select n_lote from pedidos except select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" I get this result: ERROR: rewrite: comparision of 2 aggregate columns not supported but if a try this one: "select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)" It's OK !! What's up??? Do you think i found a bug ??? Do exist some limitation like this in subqueries?? (Perhaps Postgres don't accept using aggregates in subqueries ???) I tried this too: "select n_lote from pedidos where n_lote not in (select rp.n_lote from relpedidos rp, relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad))" but the result was the same ! And i get the same error message (or similar) when i try other variations. Thanks !!! Fer -- * ** ** ** * **** * * ** ** ** * *** * * * * *** * *** * ** *** * * * * * ** ** * * * (*) SymeX ==> http://www.lantik.com (*) Web en http://www.arrakis.es/~txino (*) Informate sobre LINUX en http://www.linux.org
Re: [SQL] weird structure
Renato De Giovanni wrote: > > > > Consider the following tables/fields: > > > table "person": fields "p_id", "p_name". > > > table "person_attribute": fields "a_id", "a_name". > > > table "person_data": fields "d_person_id", "d_attribute_id", > > > "d_value". > > > > > > Also consider that a person may not have data related to all possible > > > attributes. > > > > > > Using this structure, how could I retrieve in one row with a single > > > select statement all attributes from a person (showing null to > > > attributes that were not registered to him)? > > > > > > Thanks for any suggestion, > > > -- > > > Renato > > > Sao Paulo - SP - Brasil > > > [EMAIL PROTECTED] > > > > I did similar things using the following technique: > > in frontend I gather all attributes from person_attribute and then > > generate a query like this: > > > > select *, (select distinct d_value from person_data where d_person_id = > > person.p_id and > > d_attribute_id = 'here first attribute id'), (select distinct d_value > > from person_data where d_person_id = person.p_id and d_attribute_id = > > 'here second attribute id'), ... from person > > -- > > Sincerely yours, > > Yury > > Yury... > That worked perfectly! Thank you very much! > > Tell me, I've never seen this sql construction before, is it specific to > postgres? Or should it also work on other databases? > > And is there any relevant performance issue considering this kind of > database schema and its peculiar sql commands? > > Thanks again! > -- > Renato > Sao Paulo - SP - Brasil > [EMAIL PROTECTED] This construction works in any database which support subselect in target list in "select" statement, for example I used this in Interbase. What about performance - it strongly depends on amount of attributes. In order to maximize it you need to create indexes on all fields which participate in "where" clause - p_id, d_person_id, d_attribute_id. And performance . -- Sincerely yours, Yury
Re: [SQL] Select subset of rows
On Sun, 27 Aug 2000, Stephan Szabo wrote: > > Of course immediately after sending the last message and logging off my > ISP I figured out the simpler way for the third one: > > begin; > select salary into temp saltemp from employee order by salary desc > limit 5; > select name from employee where exists (select * from saltemp where > saltemp.salary=employee.salary); > end; > > Stephan Szabo > [EMAIL PROTECTED] > I wonder if the following might be a bit faster? Or would it be slower? select salary into temp saltemp from employee order by salary desc limit 5; select min(salary) as minsal into test minsal from saltemp; select name, salary from employee, minsal where salary >= minsal; I don't know the speed difference between my second select and your subselect within an EXISTS clause might be. Just a thought, John
Re: [SQL] Select subset of rows
On Sun, 27 Aug 2000, John McKown wrote: > On Sun, 27 Aug 2000, Stephan Szabo wrote: > > > > > Of course immediately after sending the last message and logging off my > > ISP I figured out the simpler way for the third one: > > > > begin; > > select salary into temp saltemp from employee order by salary desc > > limit 5; > > select name from employee where exists (select * from saltemp where > > saltemp.salary=employee.salary); > > end; > > > > Stephan Szabo > > [EMAIL PROTECTED] > > > > I wonder if the following might be a bit faster? Or would it be slower? > > select salary into temp saltemp from employee order by salary desc > limit 5; > > select min(salary) as minsal into test minsal from saltemp; > > select name, salary from employee, minsal > where salary >= minsal; > > I don't know the speed difference between my second select and your > subselect within an EXISTS clause might be. Not sure. Probably depends on if the subplan/seq scan for the subquery on the one is more or less expensive than the time on the additional query and processing for the query with the min. Actually, i guess you might be able to use offset in there to get rid of the second query too... if you do like select salary into temp saltemp from employee order by salary desc limit 1 offset 4; you can probably get the 5th one right out.
[SQL] Sql and paradox
I want to know the incompatibilities between Paradox text files and SQL database.
