query

SQL select nth member of group

If I have a USER table like class | age -------------- 1 20 3 56 2 11 1 12 2 20 Then I can easily get the youngest user in each class via select class, min(age) from user group by class; Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in...

Performance Tuning SQL - How?

How does one performance tune a SQL Query? What tricks/tools/concepts can be used to change the performance of a SQL Query? How can the benefits be Quantified? What does one need to be careful of? What tricks/tools/concepts can be used to change the performance of a SQL Query? Using Indexes? How do they work in practice? Normalis...

MySQL: Most efficient way to count records added today

This is the MySQL table structure that I have: itemID (BIGINT) userID (BIGINT) wasAdded (DATETIME) I need to get the number of items introduced in the current day, by the X userID What would be the most efficient way to do this? Thanks ...

Team Foundation Server - Cannot add new query

In Work Items - Team Queries I click on add new query. The query appears in Team Queries , is saved in the database TFSWorkItemTracking in StoredQueries Table. When I connect with another client or when I reconnect with the client with which I added the query , it is not visible anymore. From what I have investigated the new queries I ...

Team Foundation Server - Cannot add new project

I am logged in as administrator I click on add new project on my TFS Server. I give a name to the project than select a process template for the new Team Project ( the process template is MSF for Agile Software Developement - v4.2 and it is just downloaded for the microsoft website it is not modified . I uploaded it to the TFS Server Te...

Query works several times slower when comparing BIT column with 0 instead of 1

I use a view based on a complex query with 17 joins (both inner and left/right outer) and subqueries. All view rows are shown in about 5 seconds. SELECT * FROM a_view; One of the view columns has type BIT. And when I filter view rows comparing it with 1, a query works again about 5 seconds. SELECT * FROM a_view WHERE c = 1; But wh...

What are some best practices for optimizing multiple column LIKE SQL queries?

I have a search query that I'm inheriting and attempting to optimize. I am curious to hear if anyone has any best practices and recommendations for such. The production server is still SQL Server 2000 also. The query is an advanced customer search stored procedure that accepts 5 different search criteria parameters (i.e. first name, la...

How to construct this NHibernate query

I have a one-to-many relationship. I would like to construct this query: Give me all the parents that have only one child and for this child child.Type=X Since I 'm learning, please show me the query with the Criteria API and with HQL. Thanks. And btw, is there any automatic way to know what HQL is identical to a criteria expression ?...

Xpath query and time

Hi, this is the content of my XML file: <?xml version="1.0" encoding="ISO-8859-1"?> <mainNode> <sub time="08:00"> <status id="2">On</status> <status id="3">Off</status> </sub> <sub time="13:00"> <status id="4">On</status> <status id="7">On</status> </sub> <sub time="16:00"> <status id="5">On<...

Retrieve and print data from dynamic sql query with linq

I have some existing code that retrieves data from a database using ADO.NET that I want to convert to linq. What the code does is receives an SQL query via command line, executes it, returns the rows and their column names, then prints them to the screen. I would like to know how to write this code in linq. The ENTIRE sql query MUST...

Table Filed

hi My code that can get tablename in linq query is: Dim TY As Type = (From T In DB.Mapping.GetTables() Where T.TableName = tableN Select T.RowType.Type).SingleOrDefault Dim Table As ITable = DB.GetTable(TY) Dim mq = From t In Table Select t tableN get from another form. I w...

Select Parent Record With All Children in SQL

Let's say I have two tables, "Parent" and "Child". Parent-to-Child is a many:many relationship, implemented through a standard cross-referencing table. I want to find all records of Parent that are referenced by ALL members of a given set of Child using SQL (in particular MS SQL Server's T-SQL; 2005 syntax is acceptable). For example l...

Access DB - Operation must use updateable query.

I have a website I've built in VS2005, C#, .NET 2.0. This website does transactions against an Access database. It works fine in development. The mdb file resides in App_Data I have setup IIS on this machine to test the website as it would be deployed. I published the site to my IIS virtual directory. All went well. Whenever I try to ...

Query hangs with INNER JOIN on datetime field

Hi everybody! We've got a weird problem with joining tables from SQL Server 2005 and MS Access 2003. There's a big table on the server and a rather small table locally in Access. The tables are joined via 3 fields, one of them a datetime field (containing a day; idea is to fetch additional data (daily) from the big server table to add ...

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

MySQL add total column

I need to query this DB to get each row, but also the SUM of one of the column values of the results. I could use php to get the total value, but then I'd need to run two loops, one to get the total (which goes at the top above the results). So I'd prefer the query to catch it and just make a "total" row, but the only way I've gotten it ...

How do I Display one Row for the lowest number of a column?

This question is complicated so examples would work best...I have the following table on ODBC, not SQL server Management NAME SEQNUM JOHN 2 JOHN 4 JOHN 7 MARY 12 MIKE 4 MIKE 9 PETER 7 PETER 12 So, i want to pull back one name with the lowest seqNum... NAME SEQNUM JOHN 2 MARY ...

Dynamically Changing what table to select from with SQL CASE statement

I'm trying to write a stored procedure and depending on a certain column value, I want to be able to change what table I select from. I'll try to give an example: SELECT ItemNumber, ItemType, Description FROM CASE ItemType WHEN 'A' THEN TableA ELSE TableB END WHERE CASE ItemType WHEN 'A' THEN ItemNumber = @itemNumber...

Refactor SQL (workaround RIGHT OUTER JOIN)

Hey Stackers, Since SQLite does not support RIGHT OUTER JOINS I pose the following challenge (read: invitation to do my work for me): Refactor this query so it no longer utilises SQLite-unsupported constructs like RIGHT/FULL OUTER JOINs. SELECT strings.*, translations.text FROM translations INNER JOIN ...

Is it RESTful to limit resource properties?

Our resources consists of dozens of fields (attributes). Some of our clients don't need all resource's properties. To save network payload we implemented a query string parameter to limit the resource properties. So for example, the next URL will return a collection of resources with all their fields: http://myapp/myresources But when ...