Example: SELECT `film_id`,COUNT(film_id) AS COUNT FROM films_genres AS FilmsGenre WHERE genre_id In (4) GROUP BY film_id,COUNT HAVING COUNT = 1 return: film_id | COUNT 7 1 6 1 But I want it to return: film_id 7 6 How do I return only 1 colomn? ...
I want to return the 10 most common words from a query in SQL server, so running against a set of rows such as this: quick brown fox slow yellow fox slow green fox would return fox slow quick brown yellow green ...
Hi, Can I call a scalar function within a table-valued function? Thanks ...
I have a select statement select someFields from table1,table2 which is a cartesian join, right? What's the explicit syntax for such a join. Searching the web for , isn't yielding much luck. ...
I hope I'm explaining this well. I'm struggling with this query: I have this table that is something like this: InvoiceNum Amount Type - where type could be item, shipping or tax. So what I want returned is one row per invoice: InvoiceNum, ItemAmount, ShippingAmount, TaxAmount. Here's an example: Invoicenum Amount Type 1 ...
I'm trying to write a UDF to translate a string that is either a guid or a project code associated with that guid into the guid: CREATE FUNCTION fn_user_GetProjectID ( @Project nvarchar(50) ) RETURNS uniqueidentifier AS BEGIN declare @ProjectID uniqueidentifier BEGIN TRY set @ProjectID = cast(@Project as uniqueide...
I have a server with multiple databases. I need to loop through these databases and change a value in one record, in one table, in each database. How can this be done? ...
I have a CTE as below (logic removed) ;with cte_a as ( select ID, Count(AnotherID) as Counter from Table_A group by ID ) and a user defined function that takes an Id as input and returns a table. udf_GetRelatedItemIds(@Id) I wanted to just count the number of related item ids returned from the user defined function for ...
I want to know the sql statement(s) to use to delete old data and the association to the old data without deleting the more important parent table, which should live forever. I have the following tables: Step Id bigint OccurredOn datetime ScreenshotId bigint Screenshot Id bigint Data varbinary(max) I want to delete all Scr...
Hi, Lets say I have a table that looks like this: Name value 1 Ford "some text here and there." 2 Honda "More Text Again" How can I write a sql function that will append me the 'value' column of all of the rows. so the result would be sometext here and there. More Text Again also it would be nice if I can...
My problem: I have a table with a Channel <int> and a Value <float> column, along with a timestamp and a couple of other columns with additional data. Channel is either 1 or 2, and there is either 1 or 2 rows that have everything except channel and value the same. What I'd like to do is select this data into a new form, where the two ch...
Hi, I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005) ...
I have a big query as following, someone please tweak this query? i need to avoid this big group by ... :-) SELECT COALESCE( SUM(total_retail), 0 ) total_retail, COALESCE( SUM(meterial_sub_total), 0 ) meterial_sub_total, COALESCE( MIN(po_template_group_by_code), 0 ) po_template_group_by_code, COALESCE( MIN(po_te...
I have a page on my site which has multiple drop down boxes as filters. So the SQL procedure for that page would be something like this IF @Filter1 = 0, @Filter2 = 0, @Filter3 = 0 BEGIN SELECT * FROM Table1 END ELSE IF @Filter1 = 1, @Filter2 = 0, @Filter3 = 0 BEGIN SELECT * FROM Table2 ...
I've been asked to create history tables for every table in a database. Then create a trigger that will write to the history table whenever the primary table is updated. The history tables have the same structure as the primary table, but with a couple of extra rows ('id' and 'update type') I've never done anything with triggers before...
In SQL Server 2008, I am seeing some strange behavior when ordering NVARCHAR columns; here are a few quick use cases to demonstrate: Case 1: ORDER on VARCHAR values: SELECT t.Name FROM ( SELECT CAST('A' AS VARCHAR(500)) As Name UNION SELECT CAST('-A' AS VARCHAR(500)) AS NAME ) As t ORDER BY t.Name ASC Which produces (my desired) o...
I have a stored proc with a parameter which I want to have contain an XPath expression which can then be used to retrieve a value in a select, but the r.value(...) exception message says that I can only use literals. Is this true or is there a way around this? create proc MySproc @myxml xml, @xpath nvarchar(50) as begin ...
I've got this dynamic t-sql: declare @sql nvarchar(max) set @sql = N' insert into #t SELECT row_number() over(order by getdate()) as RowNum, d.value('''+@baseDateXpath+''', ''datetime'') as basedate, pref.query(''.'') as XmlExtract FROM @content.nodes(''/*/*'') AS extract(pref) CROSS APPLY @content.nodes(''//*'') AS ...
Question How do I write a T-SQL Stored Procedure that lets me select percentages of rows between X% and Y%? So basically I would want to select the rows between 30 PERCENT and 40 PERCENT..... I know that you can do the following, but obviously that doesn't let met specify a set of rows between 2 percentages. SELECT TOP 50 PERCENT * ...
Hello guys, i have some experience with MySQL but none with SQL Server, i have a computer who host a database (SQL Server 2000) and i have local access to it. The think is that one column of the table NomAntiguedad need to be change from a given date period; I need to multiply by 0.01 all the values of the column between the values of me...