sql

Last record of Join table

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...

Optimizing SQL connection performance?

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...

Restrict number of rows retrieved in Ingres select query

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? ...

Complex insert thru Stored Procedure

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...

SQL SERVER: View to get minimum and maximum values from a table.

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...

How to determine position of row in sql result-set?

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...

Translate this SQL Query in LINQ to SQL

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 ...

How to relate two tables in a DBMS?

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? ...

Error in if condition - MS SQL server

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?

What is the Difference between SQL and SQL*Plus? ...

SQL: the semicolon or the slash?

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 (...

SQL: how do I find if the contents of a varchar column are numeric ?

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 ...

PHP: Multiple SQL queries vs one monster

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...

SQL/PHP: Table design question

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...

Java Date - Insert into database

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...

Performance between SELECT MAX(col_name) and ROWNUM = 1

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...

MAX(ID) in SQL Server 2005 returning unexpected results

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? ...

Optimizing Oracle CONNECT BY when used with WHERE clause

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...

MYSQL: Query to get previous and next video ID?

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 ...

Can I use SQL cache dependency with SubSonic?

I want to use SQL cache dependency with SubSonic. How can I do this? ...