Search results for query: *

Use a correlated sub-query to count distinct OrderNo's up to this row's OrderNo. update tablename t1 set toUpdate = (select count(distinct order) from tablename t2 where t2.OrderNo <= t1.OrderNo)

update orderstable t1 set Amt = (select max(Amt) from orderstable t2 where t2.SO = t1.SO) where not exists (select 1 from orderstable t3 where t3.SO = t1.SO and t3.Date > t1.Date) Set Amt to the highest Amt value for that SO (first...

One possible solution: select * from t1 as tm1 left join t2 as tm2 on tm1.id = tm2.id and ABS((dt1 - dt2) day(4)) = (select min(ABS((dt1 - dt2) day(4))) from t1 join t2 on t1.id = t2.id where t1.id = tm1.id...

Does the table always contain exactly one row? If not, a cursor is needed! (Or a where clause that restricts the result set to one row...) You may also have to specify access mode to allow the table update: CREATE FUNCTION INCREMENT() RETURNS INT MODIFIES SQL DATA BEGIN ...

Yeah, thats a part of the beaty of SQL, the same problem can be solved in completely different ways. (Somewhat confusing for beginners?) Note that my solution is Core SQL-99 compliant! (http://developer.mimer.com/validator/parser99/, a great time saver when writing "portable" SQL.)

I guess you want something like: SELECT * FROM table_collection WHERE IC IN (SELECT IC FROM table_collection WHERE Mode_Of_Payment = 'Cash') i.e. all rows for a transaction where cash has been involved at least once.

Have you tried: delete from table1 where type = 'pdf'

Why do you require separate fu-calls for different discharge dates? I mean, Bob Smith was discharged twice before he got his first fu-call, why does this call only count for the first discharged date? (If it doesn't count for both days, don't store that fu_call for both discharged days.)

And an EXISTS solution: SELECT * FROM CLASS_TIME_TABLE A WHERE EXISTS ( SELECT * FROM CLASS_TIME_TABLE B WHERE A.NAME = B.NAME AND A.Date = B.Date AND A.Start_time = B.Start_time AND A.End_time = B.End_time GROUP BY NAME, Date, Start_time, End_time HAVING COUNT(*) > 1) Core SQL-99...

Check out GROUP BY in combination with HAVING and COUNT(DISTINCT)! To find persons with two (or more) different id values: SELECT NameFirst, Surname, DOB FROM C GROUP BY NameFirst, Surname, DOB HAVING COUNT(DISTINCT id) > 1 The easiest way to get the result you're looking for is to add this...

First, if you really want OUTER JOIN's instead of regular INNER JOIN's, put the join conditions in the ON clauses, not in the where clause. And also put the "outer" tables conditions in the ON clause. Personally, to make things easier to read and to understand, I'd turn the query around and use...

For many years I've been using FrameMaker 6, Adobe Acrobat 4, and WebWorks Publisher 7. The documentation source is a set of 4 different documents (different FM books, 1500 pages in total), with links/cross references between them, and lots of text set as conditional. The generated output is...

Use CAST or SUBSTRING to get first 3 characters.

ANSI SQL has no built-in functionality to convert dates to the format you want. Check your specific dbms manual, it might have something you can use. In ANSI SQL you can use EXTRACT to get each specific part of a date, i.e. EXTRACT(day from market_date). But then you have to use CAST to convert...

The GROUP BY construction is typically used to "merge" several rows together into one row. Since you want one row per day, do group by the date column: SELECT market_date FROM yourtable GROUP BY market_date But for each date you do also want mid-values for 'FR.EUR.GBY.0.0.1' rows returned in a...

How many rows per day does your table have? How many rows per day do you want to be returned? How many different "EURFRGBONDnY" columns are you actually having, is it just EURFRGBOND1Y and EURFRGBOND2Y, or do you have a whole bunch of them? The better you describe your problem, the better...

Use CAST to convert values to a specific data type, e.g. cast(market_date as date). Note that the ANSI date is in format YYYY-MM-DD! It seems you want data per day? GROUP BY is used for SELECT market_date, setfn(CASE WHEN (rate_code='FR.EUR.GBY.0.0.1') THEN mid else 0 end) as...

Have you considered a view, to get both the firm_code and the firm_id? I mean skip the firm_code in the office table, instead create a view that also includes the firm_code column. create view offices_view (office_id, office_code, firm_id, firm_code, name) as select office_id...

The Oracle date/time handling isn't ANSI compliant, so I suppose you'd better ask the Oracle people, http://www.tek-tips.com/threadminder.cfm?pid=1177

First, why do you want to store firm_id AND firm_code in the offices table? (See http://en.wikipedia.org/wiki/Data_redundancy) When it comes to your insert, if you choose the trigger solution you'll need one insert trigger and one update trigger to make sure firm_id and firm_code always are...