sql

SQL query help

Hi, I wrote a query select u.user, g.group, u2g.something from users, groups, u2g where users.u = u2g.u and groups.g = u2g.g that returns data like this: user, group, something ---------------------- 1 , 3, a 1 , 5, b 2 , 3, c 3 , 3, d 4 , 5, e now I would like to limit this query in such a way th...

Analytical Query

Am looking for a single query that can convert the following information in the table name:time :state a :10:00 AM:login b :10:05 AM:login a :10:06 AM:chatting a :10:08 AM:Idle b :10:11 AM:chatting a :10:10 AM:Logout b :10:12 AM:Logout to something like this (given the time range 10 AM to 10:15 AM as the querying per...

Primary key or Unique index?

Hello, in work we have big database with unique indexes instead of primary keys and all works fine. I`m designing new database for new project and I have a dilema: in DB theory, primary key is fundamental element,it`s OK... but in REAL projects what are adventages and disadventages of both? what do you use in projects? EDIT:...and w...

SQL Server, Converting NTEXT to NVARCHAR(MAX)

I have a database with a large number of fields that are currently NTEXT. Having upgraded to SQL 2005 we have run some performance tests on converting these to NVARCHAR(MAX). If you read this article: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx This explains that a simple ALTER COL...

Whats the best build system for building a database?

This is a problem that I come to on occasion and have yet to work out an answer that I'm happy with. I'm looking for a build system that works well for building a database - that is running all of the SQL files in the correct database instance as the correct user and in the correct order, and handling dependencies and the like properly. ...

SQL UNION and ORDER BY

I have an annoying SQL statement that seem simple but it looks awfull. I want the sql to return a resultset with userdata ordered so that a certain user is the first row in the resultset if that users emailaddress is in the companies table. I have this SQL that returns what i want but i think it looks awful: select 1 as o, * from User...

Performance issue in using SELECT * ?

Is there any performance issue in using SELECT * rather than SELECT FiledName, FiledName2 ... ? ...

SQL CASE Statement Versus Conditional Statements In Programming Language

I'm going through some old stored procedures at work and constantly come across CASE MyColumn WHEN 'Y' THEN 'Yes' WHEN 'N' THEN 'No' ELSE 'Unknown' END It gets even worse when this is tied not to a word, but instead a colour. CASE MyColumn WHEN 'Y' THEN 'style="background-color:pink"' ELSE '' END The reason this was done was for ol...

What is your most useful sql trick to avoid writing more code?

I am intending this to be an entry which is a resource for anyone to find out about aspects of sql that they may have not run into yet, so that the ideas can be stolen and used in their own programming. With that in mind... What sql tricks have you personally used, that made it possible for you to do less actual real world programmin...

Passing multiple parameters to controller in ASP.NET MVC; also, generating on-the-fly queries in LINQ-to-SQL.

I'm working on a basic Issue Management System in order to learn ASP.NET MVC. I've gotten it up and running to a fairly decent level but I've run into a problem. I have a controller named Issue with a view called Open. /Issue/Open lists all of the open issues currently logged on the system. I've defined a route like so: routes.MapR...

Merging results in T-SQL statement

I have to queries: select id, name, num from pending id, name, num 1, first, 1 3, third, 12 select id, name, num from completed id, name, num 1, first, 100 2, second, 20 I want output to combine these, maybe like a pivot table, using T-SQL. id, name, pending, completed, total 1, first, 1, 100, 101 2, second, null, 20, 20 ...

SQLite 3: Mass Data Load Issue: Error 14 Database Not Found

I have an iPhone application that is a data load harness to pre-populate database with data that will be shipped in a separate application. When I kick the program off I am reading from an XML file and the records are inserted into the database. Everytime I hit the 247th record in the list the database then returns an error 14 databa...

Paging with Oracle and sql server and generic paging method

I want to implement paging in a gridview or in an html table which I will fill using ajax. How should I write queries to support paging? For example if pagesize is 20 and when the user clicks page 3, rows between 41 and 60 must be shown on table. At first I can get all records and put them into cache but I think this is the wrong way. Be...

SQL summing with difference

The SQL Server query I have is: SELECT ep.employeeID, ep.punchdate, rc.creditAmount FROM EmployeePunch ep INNER JOIN ResponderCredit rc ON rc.employeeID = ep.employeeID AND rc.punchdate = rc.creditdate ORDER BY ep.employeeID and get a result set: EmployeeID Date CreditAmount ----------- -----...

Best way to prevent a value from going negative in mysql

Hi all, We have a table that maintains account balances by recording transactions in that table. i.e. the most recent row is the account balance. When recording a withdrawal, we would like to ensure that the balance can never go negative. Our proposed solution is something like: INSERT INTO `txns` (`account_id`, `prev_balance`, `tx...

Tinyint vs Bit

I don't want to touch-off a religious war here, but there seem to be two schools of thoughts in how to represent boolean values in a database. Some say bit is the appropriate data type, while others argue tinyint is better. The only differences I'm aware of are these: bit: storage size is 1 bit, possible values are 0 or 1 tinyint: sto...

Can I use WITH in TSQL twice to filter a result set like my example?

I need to do something like this but SQL Server 2008 doesn't like it. My query is actually more complex than this and I realize this isn't the best way to accomplish what I'm doing but my focus is on the functionality of the WITH statement and not the select and where statements. WITH stuff1 AS ( select name, startdate, id from...

group_concat in informix

Am looking for a query in Informix's SQL that will simulate MySQL's group_concat function. What MySQL's group_concat does is it creates an enumeration of all members in the group. So with the data as follows: orderid:itemName:price 1:Paper :10 1:Pen :5 2:Sugar :15 and the following query: select group_conc...

How to Sum Columns From Two Result Sets in MySQL

I have the following table: ratings: ID | post_id | rating_type The rating_type field is either 'thumb-up' or 'thumb-down'. I want to get a result set telling me what the highest rated posts are. The following query gives me a result set with the number of up votes for each unique post_id. SELECT COUNT(post_id) as number_up, post_...

SQL Logical AND operator for bit fields

I have 2 tables that have a many to many relationship; An Individual can belong to many Groups. A Group can have many Individuals. Individuals basically just have their Primary Key ID Groups have a Primary Key ID, IndividualID (same as the ID in the Individual Table), and a bit flag for if that group is the primary group for the ind...