sql

SQL Distinct by ID and Latest By Date

I have the following SQL Statement. I need to select the latest record for each System.Id using the System.RevisedDate SELECT [System.Id],[System.RevisedDate], [System.Title], [System.State], [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], [Microsoft.VSTS.Scheduling.Remainin...

[SQL 2000] To Index or Not to Index

I have a database that I used specifically for logging actions of users. The database has a few small tables that are aimed at specific types of actions. This data is rarely searched, but the rowcounts of the tables are starting to climb into multi-millions. I haven't noticed a large slow down, but I want to know if indexing the table fo...

Skip certain tables with mysqldump

Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > database.sql But is there a similar way to dump all the tables except table1 and table2? I haven't found anything in the mysqldump documentat...

SQL/Database Views in Grails

Does anybody know what is the best approach to accessing a sql view through Grails (or if this is even possible)? It seems an obvious way of doing this would be to use executeQuery against the view to select a collection of rows from the view which we would not treat as a list of domain objects. However, even in this case it is not obvio...

Providex Query Performace Problem

I am running a query against a providex database that we use in MAS 90. The query has three tables joined together, and has been slow but not unbearably, taking about 8 minutes per run. The query has a fair number of conditions in the where clause: I'm going to omit the select part of the query as its long and simple, just a list of fie...

Sql Parameter Collection

I have 5 parameters and I want to send them to the method: public static SqlCommand getCommand(string procedure, SqlParameter[] parameter) { Sqlcommand cmd; return cmd } Can I send these paramters at one time like this? SqlParameterCollection prm; prm.Add(p1); prm.Add(p2); prm.Add(p3); prm.Add(p4); prm.Add(p5); sqlcommand cmd =...

Preferred database design method for assigning user roles? (Hats vs. Groups)

I have medium sized MySQL database with a primary "persons" table which contains basic contact information about every human being connected to the theatre and theatre school for which I am responsible for maintaining and developing a number of web applications. Some persons are just contacts - that is, their "persons" table record is a...

Converting small-ish Oracle long raw values to other types

I have an Oracle table that contains a field of LONG RAW type that contains ASCII character data. How can I write a query or view that will convert this to a more easily consumed character string? These are always going to be single-byte characters, FWIW. ...

SQL query to exclude items on the basis of one value

Hi, I'm pulling a list of items from one table, on the basis of their being included in another table, like this: select fruit.id, fruit.name from fruit, fruit_rating where fruit_rating.fruit_id=fruit.id group by fruit.name; That works fine -- it basically produces a list of all the fruit that has been rated by someone. But now, I wa...

PostgreSQL - Correlated Sub-Query Fail?

I have a query like this: SELECT t1.id, (SELECT COUNT(t2.id) FROM t2 WHERE t2.id = t1.id ) as num_things FROM t1 WHERE num_things = 5; The goal is to get the id of all the elements that appear 5 times in the other table. However, I get this error: ERROR: column "num_things" does not exist SQL state: 42703 I'...

What adavantages does HQL have over SQL for Group By queries

I am concentrating this question on 'reporting-type' queries (count, avg etc. i.e. ones that don't return the domain model itself) and I was just wondering if there is any inherent performance benefit in using HQL, as it may be able to leverage the second level cache. Or perhaps even better - cache the entire query. The obvious implied ...

MIN/MAX vs ORDER BY and LIMIT

Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)... SELECT MIN(`field`) FROM `tbl`; SELECT `field` FROM `tbl` ORDER BY `field` LIMIT 1; ...

Grouping SQL results by continous time intervals (oracle sql)

Hi I have following data in the table: ID-----startDate----endDate 5549 2008-05-01 4712-12-31 5567 2008-04-17 2008-04-30 1 5567 2008-05-01 2008-07-31 1 5567 2008-09-01 4712-12-31 2 5569 2008-05-01 2008-08-31 5569 2008-09-01 4712-12-31 5589 2008-04-18 2008-04-30 5589 2008-05-01 4712-12-31 5667 2008-...

Best approach to SQL Server query using distinct

Hi Folks, I've two tables: TableA and TableB, joined by TableA.TableA_Id->1..n<-TableB.TableA_Id. A simple PK-FK. I need to extract distinct TableA records given a certain condition on TableB. Here's my 1st approach: SELECT * FROM TableA A INNER JOIN TableB B ON A.idA = B.IdA AND B.Date = '2009-01-10' ORDER BY A.Id; This is nice, bu...

How to get the DateTime difference in Crystal Report

Hi, How can I get the DateTime difference between 2 DateTime fields in Crystal Report? I tried using DateDiff with not much luck, Any help/hint will be appreciated. Thanks, ...

Can I join to a table in ORACLE (10g) using a CASE clause in the ON statement (or even where clause as it's an inner join)

I'm trying to make the following code smaller. Is this possible? select a.* from table1 a WHERE a."cola1" = 'valuea1' UNION ALL select a.* from tablea1 a inner join tablea2 b on a."cola2" = b."colb2" WHERE a."cola1" = 'valuea2' and b."colb3" = 'valueb3' In effect I'm looking for records from table1 for value1 or value2, but for rec...

Query to replace a comma in SQL?

Hi, I have a table with the columns employee, address, city, state and zipcode. I have merged address, city, state, zipcode to a single column 'address' separating each field by comma. My issue is, if one of the fields is null, an extra comma will be inserted. For example if city is null the resulting value will be like address,,state...

What is the best/fastest MySQL Table Schema for temporary/rotating storage, e.g. for session management?

When it comes to writing custom MySQL database-driven PHP session management for a VERY dynamic website, what is the best (fastest read/write access) structure for your session table? Bad Example (Not Optimized): CREATE TABLE `session` ( `session_id` VARCHAR(32) NOT NULL, `session_data` TEXT NOT NULL, `t_created` DATETIME ...

SQL Server 2005, turn columns into rows.

I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions. Example: I have a table with the columns: ID int, ISO char(2), Text varchar(255). So I have this: ID ISO Text -- --- ---- 1 DE Auto 2 EN Car I'd like to get the following: ID EN DE -- --- ---- 1 Car ...

How do Views work in a DBM ?

Hi, Say that I have two tables like those: Employers (id, name, .... , deptId). Depts(id, deptName, ...). But Those data is not going to be modified so often and I want that a query like this SELECT name, deptName FROM Employers, Depts WHERE deptId = Depts.id AND Employers.id="ID" be as faster as it can. To my head comes t...