SV: [firebird-support] Re: Grouping without sorting alphabetically
Thanks Set for starting me on the right track. Please see my finalized query below which gets me to the expected result. Good to see that you got the result you wanted, Bhavbhuti! I do have two comments regarding your final result: 1) Why do you use ORDER BY within the CTEs? In the outer select, yes, there ORDER BY is useful, but within a CTE, I think ORDER BY is mainly useful in combination with window functions, which aren't available until Firebird 3. With CTEs like yours, I'd say avoid ORDER BY within the CTE. 2) Using cteFinalOrder is of course one possible option. However, I think it would be simpler to read the code if you just had joined cteTestGroups and cteTitleOrder directly in your main select. Though I do admit that is a matter of preference, and there's nothing wrong in using cteFinalOrder like you do. Set
Re: [firebird-support] How to join records
On Feb 16, 2015, at 4:22 AM, josef.gschwendt...@quattro-soft.de [firebird-support] firebird-support@yahoogroups.com wrote: we have 2 tables For each record in Table1 there are 2 records in Table2. Table1 (T1) == 1 2 Table2 (T2) T2T1 == 11 21 32 42 What is the cheapest way to get a dataset like below? T1T2aT2b === 1 12 2 34 Can't guarantee it's the cheapest, but you might try select T1.T1, max T2.T1 T2a, max T2.T2 T2B from T1 inner join T2 on T1.T1 = T2.T1 group by T1.T1 Good luck, Ann
Re: [firebird-support] How to join records
On Feb 16, 2015, at 4:22 AM, josef.gschwendt...@quattro-soft.de [firebird-support] firebird-support@yahoogroups.com wrote: we have 2 tables For each record in Table1 there are 2 records in Table2. Table1 (T1) == 1 2 Table2 (T2) T2T1 == 11 21 32 42 What is the cheapest way to get a dataset like below? T1T2aT2b === 1 12 2 34 Can't guarantee it's the cheapest, but you might try select T1.T1, max T2.T1 T2a, max T2.T2 T2B from T1 inner join T2 on T1.T1 = T2.T1 group by T1.T1 I think Ann meant select T1.T1, min(T2.T2) T2a, max(T2.T2) T2b --use quotation marks only if you want case sensitivity or special characters in the names, if not it is better to skip them from T1 join T2 on T1.T1 = T2.T1 group by T1.T1 An alternative to this if there are always two matching records, is: select t1.t1, t2_1.t2 T2a, t2_2.t2 T2b from t1 join t2 t2_1 on t1.t1 = t2_1.t1 join t2 t2_2 on t1.t1 = t2_2.t1 and t2_1.t2 t2_2.t2 Don't know how these two options compare performancewise, I guess Anns solution is quicker or equally quick. Anns solution will work if the number of matches varies (mine won't), whereas mine more easily extends to include other fields matching t2a and t2b. Needless to say, both Anns and my solution can be modified so that they will fit other situations. For more complex situations, you may want to consider EXECUTE BLOCK. Set
Re: [firebird-support] Unlock record
I am still trying to find a way to unlock a record when the PC that locked it (using SELECT ... WITH LOCK) hanged... I thought the following: The user who executed the SELECT ... WITH LOCK started a transaction. When his PC hanged, this transaction remains active. Is there a way to identify this transaction and rollback it? May be deleting or modifying something in a MON$ table or in another way? Mensaje original *Asunto: *Re: [firebird-support] Unlock record *De: *Eduardo guse...@gmail.com *Para: *firebird-support@yahoogroups.com *Fecha: *02/02/2015 17:28 Thank you for your answer. I agree with you. Firebird should allow for all/any attachment to be deleted. Closing the application to unlock the record is not the solution I am looking for. My problem is when there are many users using the application, then one of them hangs his PC with a record locked and then the only way to unlock it, is to tell every user to close the application and reinitiate Firebird service which is a very uncomfortable solution. Mensaje original *Asunto: *Re: [firebird-support] Unlock record *De: *'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] firebird-support@yahoogroups.com *Para: *firebird-support@yahoogroups.com firebird-support@yahoogroups.com *Fecha: *02/02/2015 14:49 Eduardo, I add some information to my previous mail. When I try to delete the record of MON$ATTACHMENTS, the complete message I got is the following: SQL ERROR CODE:-817 SQL ERROR MESSAGE: The insert, update, delete, ddl or authorization statement cannot be executed because the transaction is inquiry only It seems that monitoring table functions do not allow for read-only attachments to be deleted/killed. Very interesting. 1- The fact that the WITH LOCK option was used makes what otherwise should be a simple SELECT statement much more significant, but the message suggest that read-only statements should be ignored (although supported WITH LOCK is very uncommon usage, so may not have been considered) 2- IMO, the functionality should allow for all/any attachment to be deleted. The purpose of the feature is to all such attachments to be managed in such a fashion -- there should be no restrictions. Any other idea to unlock a record? Close have your application commit transaction or close your application. Sean --- El software de antivirus Avast ha analizado este correo electrónico en busca de virus. http://www.avast.com
[firebird-support] How to join records
Hi, we have 2 tables For each record in Table1 there are 2 records in Table2. Table1 (T1) == 1 2 Table2 (T2) T2T1 == 11 21 32 42 What is the cheapest way to get a dataset like below? T1T2aT2b === 1 12 2 34 Thank you very much for your help. Josef Gschwendtner
