Hey guys, how the heck do I go about doing this. I have an address book I'm making and I'm trying to figure out how to tackle my groups. I let the users rename their groups at will. But then this complicates my life ;0 I have 2 tables. groups and contacts the groups has the group names per user. the contacts has a group column that say...
Say I have Apples, Oranges and Melons, and each has a column "created_at". How would I construct an ActiveRecord / SQL query to find the 10 most recent Apples, Oranges and Melons? Is there an elegant way to do this? ...
Consider a system which has classes for both letters and people; both of these classes compose an address. When designing a database for the system it seems sensible to have a separate schema for the address but this leads to an issue; I don't know how to have a foreign key in the address table cleanly identify what it belongs to because...
I've a column that have 15 distinct values. I'd like to count how many there are of a few of them, I've come up with e.g. select a,COUNT(IFNULL(b != 1,NULL)),COUNT(IFNULL(b != 2,NULL)) from mytable group by a select a,SUM(CASE WHEN a = 1 THEN 1 ELSE 0)),SUM(CASE WHEN a = 2 THEN 1 ELSE 0)) from mytable group by a What's the bes...
Hi, I have a table SIGNUPS, where I register all signups to a specific event. Now, I would like to get all people who signed up to an event, with an extra column STATUS telling if the user is actually accepted (STATUS = "OK") or if it is in a waiting list (STATUS="WL"). I tried something like this SELECT *, IDUSER IN (SELECT IDUSER FR...
Suppose there are two indexes on a table index1 on col1,col2 index2 on col3 Please tell me, whether in below case index will help? .. where col1,col4 .. where col3,col4 .. where col1,col3 .. where col1,col2,col3,col4 *note, i wrote where clause only without specifying the conditions. Only used column are mentioned I am using D...
Hi, I have two tables for a facebook and twitter stream. Those tables have to be separated, so there's no point merging them into one table. But here's the thing. I want to have all the results from those two tables merged into one result and I cant seem to figure out how to get that working. Oh, and it has to be fast. I read something...
How can I make a big insertion with SqlBulkCopy from a List<> of simple object ? Does I to implement my custom IDataReader ? ...
Is there a specific command in C# for SQL Server in order to INSERT a lot of rows with the condition : if a row already exists in database doens't duplicate it during insertion ? Edited In a sqlbulkcopy, I'd like to avoid exception because a row is already in the table ? ...
Hi I was trying to execute grant all on Owner.table_name to APPS,ABC; but it is giving an error that grant privileges not provided. Please let me know how can i get and what is the reason. ...
I working on application which can deal with multiple database servers like "MySQL" and "MS SQL Server". I want to get tables names of a particular database using a general query which should suitable for all database types. I have tried following: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' But it ...
how can i use two or more condition on join? i want to replace this query with join version of it: select * from t1,t2 where t1.a=t2.b and t1.c=t2.d and t1.e=t2.f how could it be? ...
I have some pretty complex reports to write. Some of them... I'm not sure how I could write an sql query for just one of the values, let alone stuff them in a single query. Is it common to just pull a crap load of data and figure it all via code instead? Or should I try and find a way to make all the reports rely on sql? I have a very ...
With MySQL Boolean Full-Text Searches... http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html A leading minus sign indicates that this word must not be present in any of the rows that are returned. Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a bool...
I have a SQL Server database designed like this : TableParameter Id (int, PRIMARY KEY, IDENTITY) Name1 (string) Name2 (string, can be null) Name3 (string, can be null) Name4 (string, can be null) TableValue Iteration (int) IdTableParameter (int, FOREIGN KEY) Type (string) Value (de...
I have an Oracle table which contains data like 'Shiv------Shukla' (consider '-' as space). Now I need to write a program which leaves just one space and removes all other spaces. Here is the program which I've made but it is not giving me expected result. DECLARE MAX_LIMIT VARCHAR2(50):=NULL; REQ VARCHAR2(20):=NULL; CUR...
Sorry for the rubbish title but hopefully this will explain: Given the table name | data --------------------- 1 | 1000 1 | 2000 1 | 3000 2 | 1500 2 | 2500 2 | 3500 I want to be able to select the top( x ) for all names ordered by the data value. So if x = 2 the retur...
I want to find the average of the most recent x results. Is it possible to use an aggregate function on a limited number of results with sql server? Here is what I have tried and the errors I get: select avg(top(100) convert(float, AnalysisData)) from tab order by DatePerformed desc Msg 156, Level 15, State 1, Line 1 Incorrect s...
Hello, I know big blocks of code aren't loved much but the below piece of code is a program soneone that left long before i started wrote in VB6. This program worked until yesterday, when it suddenly decided to stop working. The program runs as a job in SQL and no one knows how SQL finds it. We where able to relocate the original code ...
I have a table (DB_TableInfo) in my DB like the following TableId Type 859374678 R 579845658 B 478625849 R 741587469 E . . . this table represents all tables in my DB. What I wanna do is to write a query to select tables of Type 'R', get their Id and return the Name of the table belonging to that Id...