sql

Quick SQL group by question.

Let's say I have a table with 2 columns number and name. number name -------------- 0 name1 3 name4 3 name2 5 name1 So the number column has repeats in it and one or more (distinct) names assigned to each number. But for example, number 3 could have the same name as number 5. How do I show all the distinct n...

this SQL appears to be giving weird results with multiple JOINS, how do I correct it?

I have several Rails Active Record Models: ContactEmails, ContactCalls, ContactPostalcards. Each one represent a completed activity: a completed email, call, or postalcard to a specific Contact, and each Contact belongs to a Company. I want a high-level summary by Company of all the Emails, Calls, and Postalcards within a specific dat...

sql query on dual

This: select * from dual connect by level <= i ...will return result as 1 2 3 ... i Can the query be modified to get result row wise? i.e 1 2 3 .....i ...

Using Aggregate Functions in a single sql query

I'm curious if it is possible to do the following select id from foo where foo.bar = (select SUM(bar) from foo ) without the use of a subquery. Edit: To clarify, I'm trying to do this with postgresql, which does not appear to support the two solutions posted thus far. ...

How to specify a relative date in MySQL?

I already know how to, for instance, get tomorrow's date in a query in SQL; either use ADDDATE(CURDATE(),1) or DATE_ADD(CURDATE(), INTERVAL 1 DAYS) (or something like that). But how would I get a date that is a bit more eccentric, for example the 10th of next month? ...

Copy data from one database into another using bash

Hello, I need to copy data from one database into my own database, because i want to run it as a daily cronjob i prefer to have it in bash. I also need to store the values in variables so i can run various checks/validations on the values. This is what i got so far: echo "SELECT * FROM table WHERE value='ABC' AND value2 IS NULL ORDER B...

Top reasons for no-sql?

Possible Duplicate: moving to NoSql Hi, What are the top reasons for using no-sql solutions rather than rdbms? Thanks! ...

Encountering ORA-00979: not a GROUP BY expression when using CASE - IN statements in sql

This works: SELECT (CASE WHEN x = 'value' THEN a.col1 ELSE nvl(a.col1, a.col2) END) FROM table1 a WHERE a.this = 'that' GROUP BY (CASE WHEN x = 'value' THEN a.col1 ELSE nvl(a.col1, a.col2) ...

In access-vba, what triggers run-time error 2046 (The command or action 'RunSQL' isn't available now)

There is an oddity in my VBA module. Sometimes I get a run-time error 2046: The command or action 'RunSQL' isn't available now. When I try it again later, without changing any code, it no longer gives the error, some time later, it does again. Does anyone know what triggers this run-time error? And how can I avoid it? I think the prob...

Problem in getting Sum

I have this data: name qty date flag --------------------------------------------- abc 255 11/10/1986 12:00:00 AM IN abc 300 11/10/2010 12:00:00 AM IN abc 12 11/10/2012 12:00:00 AM OUT abc 13 11/9/2010 12:00:00 AM OUT NULL NULL NULL NULL I ...

What bit stand for in sql ?

when i take a data type as bit and when i insert 0 or 1 in that columns of the table then it give error. so please tell me how to remove the error? ...

Select only the lowest values with Linq

Hi I got the following linq for ordering my input after lowest value. But i would like it to only output the lowest values. var sortedDict = (from entry in x where entry.Value > 0 orderby entry.Value ascending select entry); Now if it gets the following input. 3 4 2 6 2 This would be my output 2 2 3 4 6 What do I need to...

Difference between two clock hours

So the scenario is as follows, I have two columns "StartHour" and "EndHour". Both are stored as Integers. F.ex values can be StartHour : 30 (clock time 00:30) and EndHour : 630 (clock time 06:30) The output I'd like from this is 360 (minutes). I need the difference between the two fields, and I need them in minutes. I can process the...

SQL stored procedure equivalent to ODS e.InputParamters[] = x ?

Morning all, hope everyone is ok. I have an ODS that uses a combination of query string and Selecting event parameters. For the ODS, I'd input a paramater in the selection event a la: protected void oDs_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { e.InputParameters["memberid"] = memberid; } How w...

Export SQL file into Excel

Hi guys, I have a sql file which has a lot of insert statements (1000+), e.g. insert into `pubs_for_client` (`ID`, `num`, `pub_name`, `pub_address`, `pub_tele`, `pub_fax`, `pub_email`, `publ_website`, `publ_vat`, `pub_last_year`, `titles_on_backlist`, `Personnel`) values('2475','2473','xxx xxx xxx','xxx xxx, xxxx, xxxx, xxxx, ','000...

Problems converting NVARCHAR to INTEGER in SQL Server 2005 Express

Afternoon everyone, I'm having some issues converting a calculation into a INTEGER! Essentially I have the calculation; CAST(ROUND(SQRT(SQUARE(69.1*(CAST(tblPostCode.PCLat AS DECIMAL(30,15)) - "& 53.078282 &")) + SQUARE(69.1 * (CAST(tblPostCode.PCLng AS DECIMAL(30,15)) - "& -2.271495 &") * COS(CAST(tblPostCode.PCLat AS DECIMAL(30,15))...

Database structure for modelling one-way follower system like Twitter

Would this be the ideal/most optimal way to implement a one-way 'following' relationship? I have 2 tables as follows Table1: User_Info_Table UserID Name JoinDate Table2: User_Follower_Table FollowerID FollowingID Status FollowerID and FollowingID are FK constraints to the User_Info_Table UserID column The following ...

SQL atomic increment and locking strategies - is this safe?

I have a question about SQL and locking strategies. As an example, suppose I have a view counter for the images on my website. If I have a sproc or similar to perform the following statements: START TRANSACTION; UPDATE images SET counter=counter+1 WHERE image_id=some_parameter; COMMIT; Assume that the counter for a specific image_id h...

SQL Rows to Columns

I have below table structure in MS SQL AirQuoteID Name SalesValue 7 M 49.50 7 N 23.10 7 +45 233.20 7 +100 233.20 7 +250 2333.10 I want a query which can return AirQuoteID M N +45 ...

Parse SQL file to separate columns

Hi, I have a sql file which has a lot of insert statements (over 3000+). E.g. insert into `pubs_for_client` (`ID`, `num`, `pub_name`, `pub_address`, `publ_tele`, `publ_fax`, `pub_email`, `publ_website`, `pub_vat`, `publ_last_year`, `titles_on_backlist`, `Personnel`) values('7','5','4TH xxxx xxxx','xxxx xxxx, 16 xxxxx xxxxx, xxxxxxx, W...