sql

SQL - call aggregate function in sort order.

I have a custom aggregate function that concatenates items with a delimiter. How can I call this function to ensure that the result is a sorted list. Aggregate functions like sum() will have the same result no matter which order the rows are fed to it. I can't easily sort the items in the aggregate function itself because the sort ord...

SQL query a range of records that fall within a Min and Max value

For my Realty site I would also like to return the properties that fall within the user's requested price range from two drop down lists MinPrice and MaxPrice (which are also the field values). Below is my statement thus far. It is working properly aside from the above requirement. SELECT busname, email, render_pic, area,logo, ...

Joining multiple unrelated tables in MySQL

I have a site that stores <select> options in a number of tables, and extracts all of the relevant ones depending on the individual page. At the moment, I wind up with a query like this: SELECT foo FROM foo_tbl;SELECT bar FROM bar_tbl;etc. It's not really a bad problem, but I have to iterate over each select result individually. I'd l...

Complex SQL optimization vs. general-purpose language

How might I optimize this query? The schema: mysql> show columns from transactionlog; +---------------+-------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------------------------...

Any reason for GROUP BY clause without aggregation function?

I'm (thoroughly) learning SQL at the moment and came across the GROUP BY clause. GROUP BY aggregates or groups the resultset according to the argument(s) you give it. If you use this clause in a query you can then perform aggregate functions on the resultset to find statistical information on the resultset like finding averages (AVG()) ...

IN Operator SQL

I have a table called NUMS with a single column n. And I fill values 1,2,3,4,5,null in it. Now a query SELECT n FROM Nums WHERE n IN (1, 2, null) In this case I guess it's converted to SELECT n FROM Nums Where n = 1 OR n = 2 OR n = null I am also comparing n with a null value which should yield unknown and it should r...

data pivoting in SQL

Need some help about data pivoting in SQL. I have a Table which has the following data (Job Operation Material) (1 10 a) (1 20 a) (1 20 b) (2 10 c) I want to pivot and display it as (Job Material Oper1 Oper2 Oper3) (1 a 10 null null) (1 b null 20 null) (2 c ...

perfomance of single table vs. joined dual structure

This is not a question about using another tool. It is not a question about using different data structure. It is issue about WHY I see what I see -- please read to the end before answering. Thank you. THE STORY I have one single table which has one condition, the records are not deleted. Instead the record is marked as not active (the...

What's an elegant way to retrieve all times of the day (1 hour resolution) in DB2 without a backing table?

I'm querying some data from a table in DB2/z which holds hourly data but occasionally skips some hours (if the clients don't send up details for that hour). Since I'm feeding the data straight into a charting tool which needs a query to get the axis data, it sometimes has missing values from the time axis and looks ugly: 23.00 |=== 22....

[SQL] Dynamicly labeling users by score

I've got a table with users and their score, a decimal number ranging 1 - 10. Table(user_id, score) I also have a 1 row query with the average of the scores (about 6), and the standard deviation (about 0.5). I use MS Access 2007. I want to label the users A, B, C, D, where: A has a score higher than (avg+stdev); B has a score lower t...

Problem writing a database query

I have two models, Location and Event, that are linked by ForeignKey on the Event model. The models break down as follows: class Location(models.Model): city = models.CharField('city', max_length=25) slug = models.SlugField('slug') class Event(models.Model): location = models.ForeignKey(Location) title = models.CharFiel...

SQL 2005 Express not using materialized view in explicit query

I know that Express edition of SQL Server 2005 does not include materialized views (with schemabinding) automatically while estimating optimal execution plan. However when the view is queried directly - i still get underyling table displayed in actual execution plan. Why isn't my clustered index created on materialized view used in the q...

SQL Server XSD SimpleType xs:list as table?

SQL server has support for XML, but I cannot figure out how to get it to work with the list type <?xml version="1.0" encoding="utf-16"?> <xsd:schema id="XMLSchema1" targetNamespace="http://tempuri.org/XMLSchema1.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd" xmlns:mstns="http://tempuri.org/XMLS...

Drawing dynamically defined fields in MS Access

I have no idea if this is possible - and suspect it may not be. I have a table in MS Access 2003 that lists some fields that are present in another table in the database. What I want to do is draw the data from the other table but in the select statement define the fields to draw by using the values in the first table. For example Ta...

How to get Microsoft SQL MATH POWER to show as decimal and not as INT (which it seems to do)?

I have this simple query: SELECT POWER(( 1 + 3 / 100 ), ( 1 / 365 )) According to MS SQL POWER(( 1 + 3 / 100 ), ( 1 / 365 )) = 1 when in fact it's 1,000080986 How to get Power to return Decimal instead of int. This topic seems similar to http://stackoverflow.com/questions/429165/raising-a-decimal-to-a-power-of-decimal but that on...

T-Sql syntax error in Update from Select

Hi, I have a stored procedure in T-Sql with this code at the end of it: UPDATE @Results SET Percentage = CASE B.Total WHEN 0 THEN 0 ELSE (CAST(Number AS FLOAT)/B.Total) END FROM @TotalAnswersPerQuestion AS B WHERE @Results.QuestionNumber=B.QuestionNumber The temp table @Results is defined and correctly used in this store procedure jus...

SQL Server high CPU and I/O activity database tuning

Our application tends to be running very slow recently. On debugging and tracing found out that the process is showing high cpu cycles and SQL Server shows high I/O activity. Can you please guide as to how it can be optimised? The application is now about an year old and the database file sizes are not very big or anything. The database...

MySQL: Random pick that lasts for a specific period of time?

Okay... I'm lost (again). How can I make a custom seed for rand() that will keep the pick from the database for a specific period of time: 10 minutes, 1 hours, 1 day, etc. I use this: $query = 'SELECT * FROM table order by rand () LIMIT 1'; To get a random pick every time I refresh the page. I've been reading for days, but I just can...

Getting hierarchy data from a self-referencing table

Let's say you have the following table: items(item_id, item_parent) ... and it is a self-referencing table as item_parent refers to item_id. What SQL query would you use to SELECT all items in the table along with their depth where the depth of an item is the sum of all parents and grand parents of that item. If the following is the co...

WCF to Entity Framework converts my datetime into local time - how to stop?

Hi there, I've got a WCF service which receives records with datetime values, and saves them to an Azure SQL db, using Entity Framework 3.5, SP1. Running it locally, the records get saved to the database, all is well However, my IIS hosting is in a different timezone. When I upload the site to there and call the same service (with the ...