.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

can the query be written using RANKING functions

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

I am of the opinion that the query below can be written using a windowing function instead of the self join.

Can anyone help with this please.


FROm #hold a 
where TransactionDate = (SELECT MAX(TransactionDate) FROM #hold b where b.PersonURN = a.PersonURN and b.DealerID = a.DealerID)

View Complete Post

More Related Resource Links

Re-writing query using RANKING functions


I have the query below and I am trying to use historical queries to retrieve the information, I have put something together but running the Original query and my new query does not bring back thesame results, but i think I may be doing something incorrectly.


Why a word "dummy" turns blue when written in a query window?

Why a word “dummy” turns blue when written in a query window of a MS SQL Server Management Studio? What does "dummy" mean in SQL language?

SQL Query Help - Aggregate Functions Across Several Variables


I have a table that looks like this….


Size                  BillCode           Qty      Weight

4                      A                     3          230

4                      B  &nbs

Capturing the error for a query written in an Execute SQL task



I have a SSIS 2005 pkg with an execute SQL task and a query as a SQLstatement. I want to capture a possible error generated from this query. How can I do it? Using the Result set?


Why do some XML functions have such high query plan cost?


Does anyone know why this generates an expensive query plan:


SELECT @XML = '<XML Id="Hello"></XML>'

SELECT c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML') T(c);

The queryplan includes two "Table Valued Function [XML Reader]"'s to occur with a cost of 61.08 each on my 2005 sp3 developer server.  If I select out an attribute then it is considerably lower:

c.value('.', 'VARCHAR(MAX)') FROM @XML.nodes('//XML/@Id') T(c);

This results in a [XML Reader] and a [XML Reader wuth XPath filter] and the cost is 1.004 for each operator.

Running tests show that they are both quick, but I am writing a stored proc for a high transaction system and always try to keep the query plan as low as possible and would not n

Working with Ranking Window Functions

As the name suggests, ranking functions let you rank the rows in your result set based on specified values in those rows. SQL Server supports four ranking functions:

ROW_NUMBER: Assigns a sequential number to each row in the result set.
RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. However, the next number in the ranking sequence is skipped.
DENSE_RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is then used to rank the row or rows that follow.
NTILE: Divides the result set into the number of groups specified as an argument to the function. A group number is then assigned to each row identifying which group the row belongs to.

Top N PERCENT function analogue using ranking functions


Hi everybody,

I'm wondering if there is a good way to simulate TOP N PERCENT using RANKING functions? NTILE() comes close, but it doesn't simulate it exactly. It almost looks like we need a new PERCENTILE() ranking function.

Thoughts, ideas?

Thanks in advance.



Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Introduction to Functions

A function is a section of code that is used to perform an isolated assignment. Once it has performed its assignment, the function can be accessed to present its result(s).

In Transact-SQL, a function is considered an object. After creating the function object, it becomes part of a database. You can then execute it when necessary.

Built-In Functions Fundamentals

While your primary job as a database developer consists of creating lists, probably your second most important job is to assist your users with the various assignments they must perform on your application. One way you can assist is to use functions that perform otherwise complex tasks. We introduced and described functions in the previous lesson. To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

GridView control to show master-child or master-slave data, written in c#, asp.net

I previously made a master slave datagrid using Visual Studio 2003 .Net (original article) that would allow users to show data to clients using collapse and expand features. These features however did not carry over to gridview for many users. This tutorial will explaing how to create a collapsable/expandable gridview in frameworks 2.0 and higher

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Built-in Functions - Text and Image Functions in SQL Server

Text and image functions operate on TEXT, NTEXT and IMAGE data types. These data types are deprecated with SQL Server 2005 and should be replaced by VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) when possible. Alternatively you can also consider storing large string values using XML data type. Text and image functions are nondeterministic.

Future versions of SQL Server will not support TEXT, NTEXT and IMAGE data types. Columns with TEXT / NTEXT data type do not support commonly used string functions such as LEN, LEFT, RIGHT, etc. Furthermore, due to the large size of TEXT data you''re likely to see performance issues if you store such data in the database. However, at times you can''t help but use the TEXT / NTEXT / IMAGE data types (for example when supporting 3rd party databases when you cannot change the schema) - this is when text and image functions come in handy.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend