limit distinct keyword to one column
how do i apply the the distinct keyword in mysql so that it is only checking that one column field is unique, while still returning other columns from my table? ...
sql
A Simple Sql Select Query
I know I am sounding dumb but I really need help on this. I have a Table (let's say Meeting) which Contains a column Participants. The Participants dataType is varchar(Max) and it stores Participant's Ids in comma separated form like 1,2. Now my problem is I am passing a parameter called @ParticipantsID in my Stored Procedure and want ...
How might I insert an array of data into a database table?
I am developing an attendance management program, used to maintain the absence record of a student. Users of this software will need to enter various dates, updated once in a month: for instance, a list of dates on which a student was absent for that particular month would be entered, and my program must then store them into a database w...
Effective one-to-many inserts in C# and MsSql
Hi! I have two tables which looks like this: News: (ID, Title, TagID) Tags: (ID, Tag) Each news can only have one tag. What is the most effective way to handle inserts to the news table? The Tags table has like 50 000 rows. I'm only doing bulk inserts of approx. 300 news at a time, around 2 times per hour. I assume that i need some ...
Is it a good idea to learn LINQ first, then SQL?
I know that in most cases it`s more useful to learn more complicated technology/language and then easy one than vice versa. But, actually, time to do university tasks is limited. If I first learn LINQ, then go for SQL, would it be hard for me to use and learn sql? EDIT The task I need to do is to work with database and get some data from...
sql join question.
I have two tables, auctions and users. I want to select the username table from auctions where category=x, and then select the fields a, b and c from the users table, where the username field in users matches th username fields from auctions. I am trying this: SELECT AUCTIONS.USERNAME, USERS.firstname, USERS.lastname, USERS.flaggedauc...
Select Most Recent States From History Table
I have inherited a table with a structure something like this: ID Name Timestamp Data ---------------------------- 1 A 40 ... 2 A 30 ... 3 A 20 ... 4 B 40 ... 5 B 20 ... 6 C 30 ... 7 C 20 ... 8 C 10 ...
Delete the 'first' record from a table in MS SQL Server, without a WHERE condition
Is it possible to delete the 'first' record from a table in MS SQL Server, without using any WHERE condition and without using a cursor? ...
Add record/s from two tables to a third table: MS SQL Server 2005
Hi, I want to add a record/s from two tables to a third table. It is something like the following: table1: pln taskName plnHec pinDate(mm/dd/yyyy) xx 10 3/1/2008 yy 20 4/1/2008 zz 10 3/1/2008 zz 10 4/1/2008 xx 10 4/1/2008 tabl...
UML to SQL tool
Hi, is there some UML tool where I can draw my database design and generate SQL scripts for creating my database? thanks ...
FREETEXTTABLE Returning Incorrect Rank Result
Hi there, I'm trying to fix a bug with a site search function and have isolated it down to an issue with the FREETEXTTABLE function. I have a the following query: SELECT * FROM dbo.SiteContentForSearch INNER JOIN FREETEXTTABLE(SiteContentForSearch, sSearchText, 'NFC' ) AS SearchResultTable ON dbo.SiteContentForSearch.liSearchID = ...
Selecting all rows untill first occurence of given value
Hi, For following data: date|value|check 2009 | 5 | 1 2008 | 5 | 1 2007 | 5 | 1 2006 | 5 | 0 2005 | 5 | 0 2004 | 5 | 1 2003 | 5 | 1 2002 | 5 | 1 I need to select all rows from 2009 back until first occurrence of 0 in check column: date|value|check 2009 | 5 | 1 2008 | 5 | 1 2007 | 5 ...
Database design for a rating system
The application handles users and objects, users rate objects with 3 features (one rate per feature). EDIT: the last sentence is unclear : by features I mean criterias shared by all the objects How efficiently design a database for such a system ? What are the best-practices for designing a database dealing with a rating system ? wha...
Is it possible to convert this query to use a join instead of a subquery?
SELECT number, count(id) FROM tracking WHERE id IN (SELECT max(id) FROM tracking WHERE splitnr = 'a11' AND number >0 AND timestamp >= '2009-04-08 00:00:00' AND timestamp <= '2009-04-08 12:55:57' GROUP BY ident) GROUP BY number ...
Using stored procedures results in update statement
I've got a table with two columns(among others): id and created_in_variant and a stored procedure that calculates the created_in_variant value basing on the id. I'd like to do something like this: UPDATE [dbo].[alerts] SET [created_in_variant] = find_root [id] Is there a nice way to do it? ...
How can I efficiently do a database massive update?
I have a table with some duplicate entries. I have to discard all but one, and then update this latest one. I've tried with a temporary table and a while statement, in this way: CREATE TABLE #tmp_ImportedData_GenericData ( Id int identity(1,1), tmpCode varchar(255) NULL, tmpAlpha3Code varchar(50) NULL, tmpRelatedYear ...
Is there an Oracle SQL tool that builds insert statements from a result set?
Is there an Oracle SQL tool that builds insert statements from a result set? We are currently only allowed to use a tool called SQL Station. I'd like to either suggest a tool, like Rapid SQL or CrazySQuirrell, or build my own re-usable chunk of sql. ...
SQL Server 2005 stored procedure performance problem
I have the following issue: when a stored proc is called from my application, every now and then (like 1 time out of 1000 calls), it takes 10-30 seconds to finish. Typically, the sproc runs in under a second. It's a fairly simply proc with a single select that ties together a couple of tables. All the table names are set with a (NOLOC...
How can I make SQL Developer/SQL+ prompt only once for a substitution variable that occurs multiple times in a single statement?
I have a query roughly like this: select * from A_TABLE where A_COLUMN = '&aVariable' union select * from A_TABLE where B_COLUMN = '&aVariable'; But when I run it, SQL Developer prompts me for the variable twice, even though it's the same variable. If there's a way to make it prompt only once for a variable that is ...
Reference stored procedure in another database?
From a stored procedure or function in a given MySQL database, is it possible to reference a stored procedure or function in another MySQL database, as follows? SELECT some_table.field1, some_table.field2, another_database.STORED_PROCEDURE(arg), ... FROM ... WHERE ... ...