Hello, I have a following data in a table: id name alarmId alarmUnit alarmLevel 1 test voltage psu warning 2 test voltage psu ceasing 3 test voltage psu warning 4 test temp rcc warning 5 test temp rcc ceasing I'd like to show only the most recent information about every colums group (alarmId,alarmUnit), so the result ...
I have three tables [USER] --Master user table [KEYWORD] --Master keyword table [USER_KEYWORD] --[USER]-[KEYWORD] mapping table Below is the structure in my db GO --master user table CREATE TABLE [USER] ( [USERID] INT IDENTITY, [NAME] VARCHAR(50) ) GO --master keyword table CREATE TABLE [KEYWORD] ( [KEYWORDID] INT I...
string sql="", sql2 = "", rank=""; int basic_wage, rent_allowance, seniority_allowance, overtime_rate; sql += "select rank, Basic_Wage,Rent_Allowance,Seniority_Allowance,Overtime_rate from Designation_Details where Designation_ID=(select Designation_ID from Officer where Member_ID=" + id + ")"; sql2 += "select Overtime_Hours from Off...
I have a table that holds data about items that existed at a certain time - regular snapshots taken. Simple example: Timestamp ID 1 A 1 B 2 A 2 B 2 C 3 A 3 D 4 D 4 E In this case, Item C gets created sometime between snapshot 1 and 2 and somet...
Hi all, I have 2 tables as follows: tags: id version name tag_links: id version tag_id (foreign key to tags.id) I need to write an SQL statement that returns how many times each tag_id occurs in tag_links table. For example: tags: id version name -- ------- ------ 1 1 sport 2 ...
Given two data frames df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1))) > df1 CustomerId Product 1 Toaster 2 Toaster 3 Toaster 4 Radio 5 Radio 6 Radio > df...
I have an application which is based on a Postgres database and I need to be able to examine the requests the application sends of the database. I want to have Postgres log all of the queries it receives somewhere that I can examine them in order to rebuild some of its functionality in another application. Can someone recommend a simpl...
I searched for this for a while but came up empty ... hopefully someone here can help. Is there a query I can run on a database (SQL Server 2005) that will return the number of rows in each table? ...
I saved an object of type DataTable into SQL 2005 database in a field of type varbinary. I want to retrieve it back but I wasn't able to type cast it. This is how i saved it. MemoryStream memStream = new MemoryStream(); StreamWriter sw = new StreamWriter(memStream); sw.Write(dt); con.Open(); using (SqlCommand cmd = new SqlCommand("...
how to create a fresh database (everytime) before tests run from a schema file ? ...
I would like to write a procedure that creates a view depending on its parameter in SQL Server 2005. Grossly, this should look like this : CREATE PROCEDURE testprocedure @clientid float as create view testview as select * from deliveries where clientid=@clientid (but this does not work, it gives the error : Incorrect syntax near t...
I often find myself doing something along the following lines in sql server 2005 : Step1: create view view1 as select count(*) as delivery_count, clientid from deliveries group by clientid; Step2: create view view2 as select count(*) as action_count, clientid from routeactions group by clientid; Step3 : select * from view1 ...
I am learning database right now -- and some of the queries are getting just plain crazy. I know that during query optimization you may sometimes rewrite queries in different ways to minimize certain things. I am looking at a correllated subquery example and see that it results in the same resultset as a LEFT JOIN. I am thinking that...
I know enough about SQL to get my job done but reading different articles I see T-SQL, MSSQL, and SQL. Are they all the same? What are the m,ajor differences between the three? I know SQL is an ANSI standard. What about the other two? ...
I know this is subjective, but I'd like to know peoples opinions and hopefully some best practices that I can apply when designing sql server table structures. I personally feel that keying a table on a fixed (max) length varchar is a no-no, because it means having to also propogate the same fixed length across any other tables that use...
I have a mirrored database and I need to make some changes to it. Mainly, adding a view and some stored procedures. Now I know that if you make schema changes you are supposed to remove mirroring and the mirror database, make your changes to the principal, then backup-restore the principal and restore mirroring. Is this the case for s...
A dumbveloper at my work (years ago) moved the body column from our comments table to a secondary comment_extensions table as some sort of sketchy guesswork optimization. It seems ill-advised to do a join every time we want to display a comment, so I'm going to try moving that column back into our comments table and run some benchmarks....
I just starting to query XML within a SQL Server database. I am having trouble with the most basic query. Here is a simplified example. How do I return description? The SELECT statement below is what I am using, but it returns nothing. SELECT Incidents.IncidentXML.query ('data(/dsIncident/IncidentInformation/Description)') AS Descrip...
I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods: WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.query('declare namespace s="http://blah...
I ran across the following in a stored procedure: for select 1 from scan_queue where ( date_time_locked is null ) and ( scan_seqno >= :varMinScanSeqno ) and ( scan_seqno <= :varMaxScanSeqno ) group by loan_id, collateral_id, insurance_id into varNotUsed do varItemsToScan = varItemsToS...