I am lookign for the correct SQL code to join 2 tables and show only the last record of the details table. I have a DB with 2 tables, Deals DealID Dealname DealDetails DealComments dcID DealID CommentTime CommentPerson Comment There are multiple comments for each Deal, but i would like to create a VIEW that...
This is not a question about optimizing a SQL command. I'm wondering what ways there are to ensure that a SQL connection is kept open and ready to handle a command as efficiently as possible. What I'm seeing right now is I can execute a SQL command and that command will take ~1s, additional executions will take ~300ms. This is after t...
I want to know if there is a way to select a subsequent bunch of rows in select query in Ingres. For example, the first 500 rows can be selected by using the select first 500 from tablename, but if I want to select rows 501 to 1000, is there any way to do that? ...
Hi, I have 1 table with row data (No Primary Ky). and Another with Primary Key. In row data table, i have multiple records on 3 perameters. And other 10 Field with same value in all the records on these 3 pereameters. Eg. aa bb cc dd ee ff gg hh ii p1 p2 p3 V1 V2 V3 V4 a1 b1 c1 d1 e1 f1 g1 h1 i1 zz 123 03-05-2009 5 2 3...
Hi, I have a MSSQL Server table like this: id (auto-increment) amount date account_id Data are inserted throughout the day. I now need a view to get the opening and closing amounts for each account for each day. My trouble is creating a fast query to access both minimum and maximum values. Creating a view to access just the minimum...
i have a sql query: select id, name from table order by name result looks like this: 52 arnold 33 berta 34 chris 47 doris 52 emil for a given id=47 how can i determine the position in the result set? the result should be 4 because: 52 arnold 33 berta 34 chris are before (47, doris) and id=41 is on the 4th position in the resu...
How can I write a query similar to this one in LINQ to SQL SELECT Id, (Distance - someValue) as Res FROM Table WHERE Res < someOtherValue ORDER BY Res ...
I created two tables EMP and DEPT; now I want to relate these two tables. That is, one table's data will come to another table. How is it possible? ...
I am trying to create Update trigger which should be invoked only if the ReturnedOn column is clicked. I have used the following code-snippet but it generates the error: CODING: CREATE TRIGGER trg_ForUpdateOnBookIssuedDetails on BOOKISSUEDDETAILS For update as begin declare @Rows1 int,@Rows2 int if(update(ReturnedOn) begin IF EXISTS(...
What is the Difference between SQL and SQL*Plus? ...
We have been having some debate this week at my company as to how we should write our SQL scripts. Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production. Now, we had a deploy recently that failed because it had used both a semicolon and a forward slash (...
One of the columns in my table is a varchar that is supposed to contain only numeric values (I can't change the definition to a number column). Thus my SQL query: select to_number(col1) from tbl1 where ... fails because for some row the contents of the column are not numeric. What's a select query I can use to find these rows ? I'm ...
I have some really funky code. As you can see from the code below I have a series of filters that I add to query. Now would it be easier to just have multiple queries, with it's own set of filters, then store the results in an array, or have this mess? Does anyone have a better solution to this mess? I need to be able to filter by keywo...
I have two tables, one called "cart" and one called "items". Now I want cart to be able to store a list of "items" and quantities. So I created another table "cart_items" that has a field for the id of the template, the id of the item and the quantity. Is this good design, or should I add another field for the id of the "cart_item"? Or...
Hi all, I need to figure out a way to insert a record with a java.util.Date field into a database and i'm stuck. Does anyone know how i can do this? Right now i have something like. java.util.Date myDate = new java.util.Date("01/01/2009"); sb.append("INSERT INTO USERS"); sb.append("(USER_ID, FIRST_NAME, LAST_N...
Dear all, I doubt on the speed and result of below queries. Could anyone give me the explanation on them? (These queries written for Oracle database) Let say I have a table table1(ID, itemID, trnx_date, balance, ...). I want to get the latest balance of an item. Query 1: SELECT balance FROM table1 WHERE ID = (SELECT MAX(ID) from tabl...
I have a SQL Server 2005 database with a table an ID column. When I select MAX(ID), I get only 99, but the table contains ID no's from 0 to 103. It should select 103 as max, but it returns 99. Can anyone help me out? ...
Oracle START WITH ... CONNECT BY clause is applied before applying WHERE condition in the same query. Thus, WHERE constraints won't help optimize CONNECT BY. For example, the following query will likely perform full table scan (ignoring selectivity on dept_id): SELECT * FROM employees WHERE dept_id = 'SALE' START WITH manager_id is n...
I am developing a video website (PHP - MYSQL), just like youtube, in which I want to provide the functionality of Next video and Previous video. Let's say I am currently on videoId: 234 so Next and Previous video links will point to videoId: 233 and 235 respecively. My table structure is as follows: videoId videoName videoURL ...
I want to use SQL cache dependency with SubSonic. How can I do this? ...