sql

Is there a better way to sort this query?

We generate a lot of SQL procedurally and SQL Server is killing us. Because of some issues documented elsewhere we basically do SELECT TOP 2 ** 32 instead of TOP 100 PERCENT. Note: we must use the subqueries. Here's our query: SELECT * FROM ( SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM ( ...

how to get seconds from java long, I'm storing it in mssql DB as bigint

I store the long values in mssql using bigint so I could do something like this: --@start and @end are bigint set @duration = @start - @end now I need some additional operation so I could get the duration in seconds, anybody knows how ? ...

Best practices when handling data being stepped along a process

I am currently creating a Windows App for a handheld device (WM 6.0, CF 2.0, sqlce 3.5) that pulls data from the server only when connected to a dock (all the significant data is maintained/altered on the handheld til it comes back and is docked for a sync with server). The data is similiar something UPS would do in that it manages picku...

Problem with advanced distinct SQL query

Ok this one is realy tricky :D i have a this table bills_products: - bill_id - product_id - action - | 1 | 4 | add | | 1 | 5 | add | | 2 | 4 | remove | | 2 | 1 | add | | 3 | 4 | add | as you can see product with the id 4 was added at bill 1 then re...

OneToOne relation (cardinality) in LINQ to SQL with SQLMetal

Is there any possibility to set OneToOne relation (cardinality) when generate dbml with SQLMetal? By default dbml schema generated with the OneToMany relation. ...

sql set values for some variables with a select

I have something like this declare @foo bigint; declare @bar nvarchar(20); set @foo = select foo from theTable where id = 37; set @bar = select bar from theTable whre id = 37; is it possible to do this with a single select ? ...

Progressive account calculations (report) need advice - LAG func (?)

with account as ( select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_1' as bill_num, 100 as BF_sum, 400 as Payed_SUM from dual union select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_2' as bill_num, 150 as BF_sum, 50 as Payed_SUM from dual union select 'client1' as ...

How many times should I close the connection In SQL?

I have lots of method in my database class that all of them have one statement, when should I close the connection? In each method or at the end of database class? ...

C# class does not implement inherited abstract member

Hi, I am sorry if I am asking something stupid but I am completely a newbie in C# and ASP.NET. I am having an error in my code and I don't understand it. I am working on Visual Studio 2008. In this line of code: public class SQLFAQProvider : DBFAQProvider I am getting this error: Moby.Commerce.DataAccess.FAQ.SQLFAQProvider'does ...

Oracle: Check if rows exist in other table

I've got a query joining several tables and returning quite a few columns. An indexed column of another table references the PK of one of these joined tables. Now I would like to add another column to the query that states if at least one row with that ID exists in the new table. So if I have one of the old tables ID 1 2 3 and th...

SQL count() with group by not returning 0/zero records

For ease of discussion, consider this basic table (Test) in Access... ID division name role 1 1 Frank 100 2 2 David 101 3 3 John 101 4 2 Mike 102 5 2 Rob 102 7 3 Dave 102 8 3 Greg 102 I want to count the users of a certain role in a division. If I ...

SQL Indexing - Computed Column vs Field Used by Computed Column

Quick question for the DBA's out there: Say I have 2 columns on my table, IsDeleted (bit) and DeletedDate (datetime). The table contains approx 10,000,000 rows. IsDeleted is a computed column that checks to see if DeletedDate is NULL; and it returns 1 if it is not, and 0 if it is. Querying this table will mainly be done on the IsDele...

How do you query for comments stackoverflow style?

I saw this question on meta: http://meta.stackoverflow.com/questions/33101/how-does-so-query-comments I wanted to set the record straight and ask the question in a proper technical way. Say I have 2 tables: Posts id content parent_id (null for questions, question_id for answer) Comments id body is_deleted post...

SQL: Select distinct values from 1 column

I want to select distinct values from only one column (the BoekingPlaatsId column) with this query: SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam FROM table GROUP BY BewonerId, Naam, VoorNaam How do I do that in SQL Server? ...

Return value 1 on FIRST INSTANCE of unique

I have a table with Name and Area. I want to run a query that returns that table with the addition of a 'Special_COUNT' The count returns 1 on the firstmost Concat(Distinct(Name,Area); should return 0 otherwise For example: Name | Area | Special_COUNT | ABCD | US | ...

SQL query in MySQL using GROUP BY

Okay so this query should be easy but I'm having a bit of difficult. Let's say I have a table called 'foo' with columns 'a', 'b'. I'm trying to figure out the following in one query: select how of column 'a' are available of type column 'b', this is done with the following: mysql> select count(a),b from foo GROUP BY b; that's straigh...

Select newest entry from a joined MySQL table

I have stock quantity information in my database. 1 table, "stock", holds the productid (sku) along with the quantity and the filename from where it came. The other table, "stockfile", contains all the processed filenames along with dates. Now I need to get all the products with their latest stock quantity values. This gives me ALL th...

Simple Math max function in MySQL

How to find the maximum of two explicit values in MySQL? Something like MAXIMUM(1, @foo). There are group functions like MAX, MIN, AVG, etc that take column name as an argument and work with result sets. Is it possible to convert two explicit values to a result set and use those functions? Some other ways? P.S.: I need a max function f...

Dealing with ugly SQL in Java

Here goes a SQL-Java coding style question... How do others here deal with creating complex custom queries in Java cleanly? I am speaking of the seemingly simple task of preparing a string which is the SQL statement to be executed. I am aware of HQL, and also of stored procedures, but honestly I don't really love these solutions. Perh...

a SQL aggregation query

Hi there, I have an table with 2 columns, a date column and an int column and I want to sum the int columns per month where a month start on the 15th So the first result would be from today to the 15 of next month (Jan), the next row will be from the 16Jan to the 15Feb and so on until there are no more dates in the first column Makes ...