sql

Select highest rated, oldest track

I have several tables: CREATE TABLE [dbo].[Tracks]( [Id] [uniqueidentifier] NOT NULL, [Artist_Id] [uniqueidentifier] NOT NULL, [Album_Id] [uniqueidentifier] NOT NULL, [Title] [nvarchar](255) NOT NULL, [Length] [int] NOT NULL, CONSTRAINT [PK_Tracks_1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STA...

Selecting multiple fields into multiple variables in a MySQL stored procedure

I am a little new to store procedures in MySQL and was wondering if I can SELECT multiple columns into multiple variables within the same select query. for example (iName is the input to the function): DECLARE iId INT(20); DECLARE dCreate DATETIME; SELECT Id INTO iId, dateCreated INTO dCreate FROM products WHERE pName=iName; Thank...

MySQL function to compare values in a db table against the previous

Iam quite new to functions in SQL and I would like to create a function to compare values in a MySQL table against previous and I am not sure how to do this. For example (iId is the input value) DECLARE pVal INT(20); DECLARE val INT(20); SELECT price INTO pVal FROM products WHERE Id=iId; SELECT price FROM products; IF price == pVal...

Performance improvement to a big if clause in SQL Server function

I am maintaining a function in SQL Server 2005, that based on an integer input parameter needs to call different functions e.g. IF @rule_id = 1 -- execute function 1 ELSE IF @rule_id = 2 -- execute function 2 ELSE IF @rule_id = 3 ... etc The problem is that there are a fair few rules (about 100), and although the above is fai...

help with tree-like structure

I've got some financial data to store and manipulate. Let's say I have 2 divisions, with offices in 2 cities, in 2 currencies, and 4 bank accounts. (It's actually more complex than that.) I want to show a list like this: Electronics Chicago Dollars Account 2 -> transactions in acct2 in $ in chicago/electronics ...

How do I Put Several Select Statements into Different Columns

I basically have 7 select statements that I need to have the results output into separate columns. Normally I would use a crosstab for this but I need a fast efficient way to go about this as there are over 7 billion rows in the table. I am using the vertica database system. Below is an example of my statements: SELECT COUNT(user_id)...

Dealing with large number of text strings

My project when it is running, will collect a large number of string text block (about 20K and largest I have seen is about 200K of them) in short span of time and store them in a relational database. Each of the string text is relatively small and the average would be about 15 short lines (about 300 characters). The current implementati...

aspx connet to sql 2008 in another ip by win authorization

This is local connection for my DB <add name="connectionString" connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ViaMura;Data Source=.\SQL2008" providerName="System.Data.SqlClient"/> Can i connect to the same DB in different windows by IP? I want to connect by windows authorization not by username...

Minimizing SQL queries using join with one-to-many relationship

So let me preface this by saying that I'm not an SQL wizard by any means. What I want to do is simple as a concept, but has presented me with a small challenge when trying to minimize the amount of database queries I'm performing. Let's say I have a table of departments. Within each department is a list of employees. What is the most e...

Very difficult SQL query

For the following table definitions: Name Null? Type Comments ------------------------------- -------- ---- ------------------------------------ ENUM NOT NULL NUMBER(4) ENUM should not exceed a length of 4. ENAME CHAR(15...

Asynchronous callback - gwt

Hi, I am using gwt and postgres for my project. On the front end i have few widgets whose data i am trying to save on to tables at the back-end when i click on "save project" button(this also takes the name for the created project). In the asynchronous callback part i am setting more than one table. But it is not sending the data prope...

What is so bad about using SQL INNER JOIN

Every time a database diagram gets looked out, one area people are critical of is inner joins. They look at them hard and has questions to see if an inner join really needs to be there. Simple Library Example: A many-to-many relationship is normally defined in SQL with three tables: Book, Category, BookCategory. In this situation, Ca...

Avoiding repeated subqueries when 'WITH' is unavailable

MySQL v5.0.58. Tables, with foreign key constraints etc and other non-relevant details omitted for brevity: CREATE TABLE `fixture` ( `id` int(11) NOT NULL auto_increment, `competition_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `scheduled` datetime default NULL, `played` datetime default NULL, PRIMARY KEY (`id`) ); ...

SQL - Select all when filter value is empty

I have a SQL query in my ASP.net web app that looks like this: SELECT * FROM [Records] WHERE ([title] LIKE '%' + @title + '%') @title, of course, is the value of a text box on the page. My question is, why, when the text box is empty, does this return nothing? And how can I make it return everything, like logic tells me it ought to? ...

SQL Server Reporting Services Change report layout to Portrait

Im writing a report with Reporting services that is almost always only going to be used as exported to PDF, but the PDFs i get are always in Landscape mode and i want them in Portrait (but only for this report) Is there a way to change it between these pages layouts? ...

What is wrong with this SQL query? (or perhaps why doesn't MySQL like the sixth field?)

Here is my table: id int(11) name varchar(255) description text highest_bidder int(11) value varchar(255) group int(11) I originally didn't have the group field in there but added it later. Anyway, I go to insert into the database using the following snippet: INSERT INTO ...

UNIQUE and PRIMARY KEY

Is declaring an attribute of a table as UNIQUE equivalent to declaring it as PRIMARY KEY? thanks a lot! ...

Whats a difference between drop and delete database??

Hi, Will any one clear this.. ...

performance issue in a select query from a single table

Hi , I have a table as below dbo.UserLogs ------------------------------------- Id | UserId |Date | Name| P1 | Dirty ------------------------------------- There can be several records per userId[even in millions] I have clustered index on Date column and query this table very frequently in time ranges. The column 'Dirty' is non-n...

How to take first 4 time for each person.

Using Access Database Table ID Time 001 100000 001 100005 001 103000 001 102500 001 110000 001 120000 001 113000 ..., From the above table, i want to take first four time Query like Select id, min(time) from table group by id I want to take first four min(time) for each person Expected Output ID Time 001 100000 001 10...