I want to sort a table in sql server. Condition that i need to fulfill is this I have a table that has some records in it like this Select One None Child Old Neutral .. .. .. i want it to be sorted in such a way that Select One comes up and None comes at the end and remaining gets sorted alphabetically. Select One Child Neutral Old ....
I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem. I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error Convers...
I have a table that contains the next columns: ip(varchar 255), index(bigint 20), time(timestamp) each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute: SELECT ip, index FROM users WHERE ip...
Can I assign a value with EXISTS in the query. Currently it is giving me error Incorrect syntax near '='. But when I uses this query only it runs as expected. IF EXISTS (SELECT @PK_LOGIN_ID=PK_LOGIN_ID FROM dbo.M_LOGIN WHERE LOGIN_NAME=@LOGIN_NAME AND PASSWORD=@PASSWORD AND FK_ROLE_ID=@FK_ROLE_ID) RETURN @PK_LOGIN_ID ...
I have a table that contains some data given below. It uses a tree like structure i.e. Department SubD1, SubD2 ..... PreSubD1, PreSubD1... PreSubD2, PreSubD2... pk_map_id preferences ImmediateParent Department_Id -------------------- ---------------...
I thus far used concatenated Id string like 1,2,3 and updated in my table using this query... if exists( select ClientId from Clients where ClientId IN (SELECT i.items FROM dbo.Splitfn(@Id,',') AS i)) begin update Clients set IsDeleted=1 where ClientId IN (SELECT i.items FROM dbo.Splitfn(@Id,',') AS i) select 'delete...
I have a table (that relates to a number of other tables) where I would like to filter ONE of the columns (RequesterID) - that column will be a combobox where only people that are not sales people should be selectable. Here is the "unfiltered" query, lets call it QUERY 1: SELECT RequestsID, RequesterID, ProductsID FROM dbo.Requests I...
I have this Query: SELECT p.ProductName, dt.MaxTimeStamp, p.Responsible FROM Product p LEFT JOIN (SELECT ProductID, MAX(TimeStamp) AS MaxTimeStamp FROM StateLog WHERE State = 0 GROUP BY ProductID, State) dt ON p.ProductID = dt.ProductID ORDER BY p.ProductName; It works ...
I want to compute result from this table. I want quantity 1 - quantity2 as another column in the table shown below. this table has more such records I am trying to query but not been able to get result. select * from v order by is_active desc, transaction_id desc PK_GUEST_ITEM_ID FK_GUEST_ID QUANTITY TRANSACTI...
I have a hard time finding a good question title - let me just show you what I have and what the desired outcome is. I hope this can be done in SQL (I have SQL Server 2008). 1) I have a table called Contacts and in that table I have fields like these: FirstName, LastName, CompanyName 2) Some demo data: FirstName LastName CompanyName...
I am trying to run a query that selects values from a table using a WHERE clause , the query only returns the rows where all of the conditions have values, hoe do I go about returning the values that are also null? Language Table Students Table ID Language ID Student L...
I'm fetching data for my grid like this SELECT Orders.CustomerID, Orders.OrderTime, OrderItems.ProductID, OrderItems.Quantity FROM dbo.Orders INNER JOIN dbo.OrderItems ON Orders.ID = OrderItems.OrderID I also need the total count for the pagination. There're two options. 1- Do an another fetch SELECT count...
Hi, I need to move some code from C# into a Stored Procedure for speed reasons. What I'm trying to get is a unique list of TemplateIds from the RoleTemplates (or CategoryToRoleTemplate) table based on a CategoryId. However, I need the query to walk the Category.ParentId relationship, and collection all of the parent's related TemplateId...
By dynamic I mean I want to be able to change the sql query based on user input. Say if this is my custom query, how do I change it so that it toggles between ORDER BY .. Descending and Ascending when a user clicks the column? Is this even possible when you override the query that Views generates? <?php function views_views_pre_execute...
I am implementing a full text search using a view vw_SearchSite which has all the searchable fields and returning sites which have site IDs in common with the search results. the query: ..... <return alias="site" class="Site"/> SELECT DISTINCT {site.*} FROM v_Site {site} WHERE {site}.Id IN ( SELECT Id FROM vw_SearchSite ...
I am implementing a full text search using a view vw_SearchSite which has all the searchable fields and returning sites which have site IDs in common with the search results. the query: ..... SELECT DISTINCT {site.*} FROM v_Site {site} WHERE {site}.Id IN ( SELECT Id FROM vw_SearchSite WHERE CONTAINS(vw_SearchSite.*,:p...
I have an sql-query define on my nhibernate mapping file, that call an stored procedure to select some records. <sql-query name="sp_MYSP"> exec MYDBSP :param1, :param2, :param3 </sql-query> On code, I call the named query in this way: IQuery myQuery= Session.GetNamedQuery("sp_MYSP"); myQuery.SetString("param1", p1); myQu...
There is a customer table. I want to list active and deactive status in one query. How can I do this ? SELECT count(*) as ACTIVE, count(*) as DEACTIVE FROM V_CUSTOMER WHERE STATUS='a' AND STATUS='d' ...
I have multiple users submitting comments on multiple blog posts. Users can comment multiple times on each blog post. I need a SQL Query (sql server 2008) to get the last comment for each User given a BlogPostId. Lets say 3 users submit a total of 10 comments on a specific blog post. For Blog Post #1, User A has submitted 5 comments,...
My SQL-Query should return a previous record(Claim). Previous means that it has a different primary key (idData), an equal SSN_Number and an earlier Received_Date. The problem is that the Received_Date could be equal so I have to look for another column. The priority should be the same as the Sort-Order. What am I doing wrong, because th...