Get last value based on an Id in SQL Server 2005
Consider my query, Select EmpId,RemainingBalance from Salary where EmpId='15' My results pane, 15 450.00 15 350.00 15 250.00 How to get last RemainingBalance amount (ie) 250.00... ...
sql
Query with Ruby on Rails
Hi: I'm struggling with a rails query. Finally, I want to make a JSON response with the following contents: A listing of all countries which have at least one company, associated through "Address" and a count of how of how many companies reside in a country. Here is my simple model structure: class Country < ActiveRecord::Base has_...
Stored procedure in SQL Server (order by desc)?
I have a stored procedure that will give the latest records i.e., order by added date this is my procedure.... select distinct top 5 videos.videoid,videos.videotitle,videos.videoname, convert(varchar,videos.posteddate,106) as posteddate,videos.approvedstatus, videos.videoimage,(ISNULL(videos.views,0.0)) as [views],videos.privac...
Hierarchical Database, multiple tables or column with parent id?
Hi, I need to store info about county, municipality and city in Norway in a mysql database. They are related in a hierarchical manner (a city belongs to a municipality which again belongs to a county). Is it best to store this as three different tables and reference by foreign key, or should I store them in one table and relate them w...
SQL comma delimted column => to rows then sum totals???
Hello All, I am using MS SQL 2005 I have a problem, which currently I am battling to get a solution. I have a Table, with a these columns : NameList;Time The Namelist Column has comma delimited data in it. The table data is as such: Namelist Time John Smith, Jeremy Boyle, Robert Brits, George Aldrich 5 John Smith, Peter Hanson ...
Linq to sql Error "Data at the root level is invalid " for nullable xml column
I have Created a table like CREATE TABLE [dbo].[tab1]( [Id] [int] NOT NULL, [Name] [varchar](100) NOT NULL, [Meta] [xml] NULL, CONSTRAINT [PK_tab1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ...
Problem joining tables where joined table needs to be ordered before grouping
I have a scenario, which is seemingly simple on paper, but I'm having trouble getting to work as desired in practice. I have two tables (only relevant columns presented): | Thread +---------- | ThreadID | Post +---------- | PostID | ThreadID | Posted (Datetime) Now, what I want to do, is to join Thread and Post, grouping by ThreadI...
SQL Server 2005 replication trigers - how to?
Hi, I need to run the replication of one table in SQL Server 2005. Replication should be one way. From 1 master server to many children servers. I thougt about snapshot replication, but I don't want to schedule it only for example every hour/minute ect. (I know how to do this.) but ALSO triggered it evry time new data appears in master...
Best practices for inserting/updating large amount of data in SQL 2008
Hi, I'm building a system for updating large amounts of data through various CSV feeds. Normally I would just loop though each row in the feed, do a select query to check if the item already exists and insert/update an item depending if it exists or not. I feel this method isn't very scalable and could hammer the server on larger fee...
getting started with microsoft sql products
yesterday i went for an interview to be a sql / .net developer. my experience with sql is limited to basic pl/sql with oracle. they drilled me "do you know ssrs, do you know tsql, etc" well i kept saying no because i havent worked with them. question: what do i have to learn in order to be able to work with microsoft sql? is it really ...
How to combine two queries as a single result set in SQL for Categories - Subcategories?
I have the following function: CREATE FUNCTION [dbo].[ListStockBySubCategory] ( @CategoryID varchar(10), @SubCategoryID varchar(10), @startRowIndex int, @maximumRows int ) RETURNS TABLE AS RETURN ( SELECT ISBN FROM ( SELECT ISBN, ROW_NUMBER() OVER(AddedDate DESC) AS RowNum FROM ( SELECT DISTINCT RTRIM(...
How to split cell content and extract information into a new column in an SQL select statement?
I have a table where one of the columns are storing key/value pairs separated by semicolons, like this: KEY1:VALUE1;KEY2:VALUE2;KEY3:VALUE3 I would like to construct a view where I have additional columns where the value will be extracted from the field above. My question is how to extract VALUE1, VALUE2 and VALUE3 in a SELECT query....
How can I select non-unique fields and their sum in db2?
I need to select unique codes and their sum(value) values. This does not work: select distinct t1.code, ( select sum(t2.value) from table2 t2 where t2.code = t1.code ) from table1 t1 example table: Code Value ABC 1 ABC 2 BCD 12345 expected: Code Value ABC 3 BCD 12345 actual: Code Value ABC (null) BCD (null) Thanks...
Caching SQL execution plans
I know SQL server 2005 does some amount of execution plan caching, but would that be enough to create a difference of hours between the same query being run twice? The first time it takes 3 hours the next time it takes 1 min? Is that even possible? ...
How do you recommend I handle a database design where a USER can have many SKILLS?
Here's my database design: USER SKILLS id idUser email 1-------* description name selfAssesedLevel lastname yearsExperience ?skills? reference How could I handle this? I'm kind of lost on what I have to write down in ?skills? field of the USER table. ...
How to schedule a stored procedure?
How do I schedule a stored procedure in Sql Server 2005 that it runs once at the start of every month (and on database startup)? ...
Postgres: Is there a way to tie a User to a Schema?
In our database we have users: A, B, C. Each user has its own corresponding schema: A, B, C. Normally if I wanted to select from a table in one of the schemas I would have to do: select * from A.table; My question is: Is there a way to make: select * from table go to the correct schema based on the user that is logged in? ...
Oracle :Compare dates only by the Hour
We have a table which has a DATE column d. I'd like to get all the rows where the d column is greater / lower than some value, regardless of the date. For example | d | ------------------- |2009/11/1 15:55:23| -------------------- |2009/11/2 15:55:23| -------------------- |2009/11/3 15:55:23| -------------------- |2009/...
Why does a change of Session State provider lead to an ASPx page yielding garbage?
I have an aspnet webapp which has worked very well up until now. I was recently asked to explore ways of making it scale better. I found that seperation of database and Webapp would help. Further I was told that if I changed my session providing mechanism to SQLServer, I would be able to duplicate the Web Stack to several machines ...
Generating order statistics grouped by order total
Hopefully I can explain this correctly. I have a table of line orders (each line order consists of quantity of item and the price, there are other fields but I left those out.) table 'orderitems': orderid | quantity | price 1 | 1 | 1.5000 1 | 2 | 3.22 2 | 1 | 9.99 3 | 4 | 0.44 3 ...