How to find third or nth maximum salary from salary table(EmpID, EmpName, EmpSalary) in optimized way? Answer Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):
Tag: sql-server
SQL Server Profiler deprecation – Replacement?
I am developing ASP.NET and SQL Server applications, sometimes i am having trouble with a SQL Query, and i would like to see the SQL Servers “response” and not just the ASP.NET error message (Which is not always very helpfull) The Profiler.exe tool in SQL Server is capable of this, but im reading …
SQL SERVER: Check if variable is null and then assign statement for Where Clause
I am trying to achieve something like the below in WHERE clause in sql. I tried the following: which is wrong. Can anyone help in framing the exact statement. Thanks! Answer Isnull() syntax is built in for this kind of thing. For your example keep in mind you can change scope to be yet another where predicate…
SQLite, insert variable in my table (c# console app)
I was wondering if it is possible to insert a variable in my SQLite table (like the TheName in the example code) , and if it is how are you able to do it ? Answer You need parameterized queries:
Joining multiple tables in SQL
Can sombody Explains me about joins? Inner join selects common data based on where condition. Left outer join selects all data from left irrespective of common but takes common data from right table and vice versa for Right outer. I know the basics but question stays when it comes to join for than 5, 8, 10 ta…
Convert Rows to columns using ‘Pivot’ in SQL Server
I have read the stuff on MS pivot tables and I am still having problems getting this correct. I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic,
How to kill/stop a long SQL query immediately?
I am using SQL server 2008 and its management studio. I executed a query that yields many rows. I tried to cancel it via the red cancel button, but it has not stopped for the past 10 minutes. It usually stops within 3 minutes. What could the reason be and how do I stop it immediately ? Answer What could
Throw exception from SQL Server function to stored procedure
I have stored procedure in SQL Server 2012 say spXample and a scaler-valued function say fXample. I call a function fXample from spXample. Can I throw an exception in function and catch it in stored …
check if the column value exists in subquery
i have 3 tables Product Category and ProductCategory. Product table: Category table: ProductCategory: I need a query which returns products which fall under more than 1 categories. Based on the table data above the result would be: So i wrote a query to fetch all the ProductID’s which have more than one…
query for first and last day of month
If I have the name of the month, how can I have the first and last day of that month in SQL? I have this query to returns the month names: Result: Now, how can i get the first and last day of that months? changing the query. Answer Try this :- Result :- Result obtained taking the help from