Just like every month Ahmedabad and Gandhinagar SQL Server User Group meeting was held on Dec 19, 2009, at Ahmedabad. The interactive meeting was huge success as we had wonderful audience. We had three speakers this time.
Tejas Shah talked about “Write CROSS TAB Query with PIVOT”. Tejas is an excellent SQL Expert and a very talented individual. It gives me great pleasure when I see any UG member who updates himself to next level. Tejas has earlier presented many sessions at UG, but this was one of the best sessions. He started with a very basic example and then took it to very complex level in 30 min. He made sure that every attendee understood the subject properly.
His session was followed by a quick note regarding the tips and tricks of SQL server by myself. I talked about few myths amongst developers regarding the working with SQL Server. I always enjoy talking about a subject where the results are unexpected even though the code looks very simple.
My session was followed up by a panel discussion between Pinal Dave (myself) and Jacob Sebastian (SQL Server MVP). We talked about the recent happenings in SQL Server world as we answered few questions from the attendees.
Overall, the event was very satisfying and it was great to meet the SQL Enthusiasts in the city.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
hai pinal
i have a salary column in employees table with values as
30000
20000
20000
20000
20000
18000
18000
17000
16000
15000
14000
13000
12000
12000
10000
using stored procedure by passing parameters i want to display the highest salary
ax,if we give 1 as parameter 30000 must display and if give 2 then 20,000 must be displayed
pls provide me the solution
Thanx in advance
Hello Senjay,
Use the below script:
DECLARE @Level int
SET @Level = 2;
WITH CTE (RN,Salary)
AS
(SELECT DENSE_RANK() OVER(ORDER BY Salary DESC) AS RN, Salary FROM Employee)
SELECT top 1 * FROM CTE WHERE RN = @Level
In your stored procedure, pass a value in @Level variable using parameter.
Regards,
Pinal Dave
Hi senjay,
the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
In case one uses RANK() function, in cases of rank being tied, it does not return consecutive integers.
Hi Senjay
You can also use
This query works in AdventureWorks2008.
DECLARE @N smallint
SET @N = 10
Select distinct PP_o.StandardCost
from Production.Product PP_o where (@N-1) = (select count(distinct(PP_i.StandardCost))
from Production.Product PP_i
WHERE PP_o.StandardCost < PP_i.StandardCost)
Thanks — Vijaya Kadiyala
dear sir
i am working in MLM com which plan having giving payout 1 is 2 or 2 is 1 payout scheme.
means one person introduce in left side 1 and right side 2 or left side 2 and right side 1
i stored user table in following way
userid name parentid positionid
1 kamal null null
2 sunil 1 1
3 rajesh 1 2
4 ramesh 1 2
5 amit 2 1
6 rahul 2 1
7 abhishek 2 2
in this table only two person who are eligible to payout
kamal and sunil
but i am unable to write the query please help me for write the sql server query
thanks