Here is my scenario, I have query that returns a lot of fields. One of the fields is called ID and I want to group by ID and show a count in descending order. However, since I am bringing back more fields, it becomes harder to show a true count because I have to group by those other fields. Here is an example of what I am trying to d...
I've got a student table and an enrollment table; a student could have multiple enrollment records that can be active or inactive. I want to get a select that has a single student record and an indicator as to whether that student has active enrollments. I thought about doing this in an inline UDF that uses the student ID in a join to ...
I am looking for information on the best practices or project layout for software that uses SQL embedded inside VB.NET or C#. The software will connect to a full SQL DB. The software was written in VB6 and ported to VB.NET, we want to update it to use .NET functionality but I am not sure where to start with my research. We are using V...
From a database backup I have records without unique Ids. Some records have unique IDs. Some records with duplicated IDs contains different DateCreated values. Some records with duplicated IDs contains the same DateCreated values. I'm trying to get a MSSql 2005 query will leave only unique ID values with the most recent DateCreated v...
I have a database table containing a queue of work items processed concurrently by multiple readers, with these requirements: Each item should be processed by one reader only. If a reader fails for any reason, the item should be returned to the queue for another reader to process. Here is the pseudo code of a possible solution, each ...
I am having a problem determining how c# and LINQ solve the common problem of handling a data structure that does not necessarily return a table structure, but instead a resultset. I have a stored procedure that works, and have included it in my DBML [Function(Name="dbo.p_GetObject")] public int p_GetObject([Parameter(Name="ObjectType"...
The box this query is running on is a dedicated server running in a datacenter. AMD Opteron 1354 Quad-Core 2.20GHz 2GB of RAM Windows Server 2008 x64 (Yes I know I only have 2GB of RAM, I'm upgrading to 8GB when the project goes live). So I went through and created 250,000 dummy rows in a table to really stress test some queries that L...
Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server? ...
Consider my Event class, and that i store DateTime's in the DB as UTC dates. I simply want to return a filtered range based on the current date in a particular time zone - easy right? This works fine: IQueryable<Event> test1 = this.GetSortedEvents().Where(e => e.FinishDateTime.Date >= DateTime.UtcNow.Date); This also works fine: IQ...
Hi, I'm having a table (an existing table with data in it) and that table has a column UserName. I want this UserName to be unique. So I add a constraint like this: ALTER TABLE Users ADD CONSTRAINT [IX_UniqueUserUserName] UNIQUE NONCLUSTERED ([UserName]) Now I keep getting the Error that duplicate users exist in this table. But I ha...
I have two tables: TableA (ID [int, pk], Name [string]) and TableB (ID [int, pk], TableA_ID [int, fk], Name [string], DateStamp [datetime (dd/mm/yyyy hh:MM:ss)]). There is a one-to-many relationship between TableA and TableB. An inner join on the two tables will give me the following results: TableA.ID, TableA.Name, TableB.Name, Table...
Recently i moved to office 2007. im trying to import excel file to sql server 2000 using import and export data tool ,the tool gave data source as excel 97 - 2000 but while importing xlsx file ,im getting error "Error source Microsoft Jet databse Engine", external table is not in the expected format. Can you tell me how can i import off...
I am trying to find a way, if possible, to use IN and LIKE together. What I want to accomplish is putting a subquery that pulls up a list of data into an IN statement. The problem is the list of data contains wildcards. Is there any way to do this? Just something I was curious on. Example of data in the 2 tables Parent table ID O...
When I try to run the following statement, an error message is returned: Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'vendortofaultypeitemsmap'. SQL Help indicates this message appears when "an object that does not exist is referenced". This table does exist and returns values if I run *select * from vendortofaulttyp...
How can I be sure that my result set will have a first and b second? It would help me to solve a tricky ordering problem. Here is a simplified example of what I'm doing: SELECT a FROM A LIMIT 1 UNION SELECT b FROM B LIMIT 1; ...
Short: From below sql select I get the cart_id and the value of the maximum valued item in that cart. select CartItems.cart_id, max(ItemValues.value) from CartItems inner join ItemValues on CartItems.item_id=ItemValues.item_id group by CartItems.cart_id but I also need item_id for that item (ItemValues.item-id). Long: Two tables, C...
Say I have the following table: Create Table Comments ( ID Int Identity(1,1) Not Null Primary Key Clustered, Comment Text Not Null ) Since I'm superstitious, how can I stop multiples of 13 appearing in the ID column?ie: Skip 13, 26, 39 etc. Solution in MySQL or MSSQL much appreciated. ...
Derby doesn't support this syntax: UPDATE x SET (col1, col2, ...) = ( SELECT a,b,... FROM y ... ) (see this bug). Does anyone have a workaround other than creating a loop in Java which does the select and sends N updates back? [EDIT] Note that I have 50 columns and a complex condition (joins with x and EXISTS and whatnot). So I'dd li...
I was given good direction to solve a problem today from here but I got stuck in trying to follow this direction. I would like to retrieve a count for all rows from a table for the past 30 days using my date field. I populate these fields using "now()" and they are in the format "2009-03-04 14:58:01". I need a count for the rows added...
In sybase is there any built in functions to convert a seconds value into hours, minutes, and seconds? ...