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