Rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values,in Which Case The Same Ranking Is Assigned And A Gap Appears In The Sequence For Each Duplicate Ranking. Row_number:-returns A Unique Number For Each Row Starting With 1.for Rows That Have Duplicate Values,numbers Are Arbitarily Assigned. Dense_rank:-assigns A Unique Number For Each Row Starting With 1,except For Rows That Have Duplicate Values ,in Which Case Same Ranking Is Assigned.
Ex #1
========== - select ename,empno ,sum(sal) from emp where deptno=10 group by ename,empno order by ename,empno;
Ex #2
========== -
select ename,empno ,sum(sal) SALARY,
-
rank() over(order by sum (sal) desc) RANK,
-
dense_rank() over(order by sum (sal) desc) SALARY_DENSE,
-
ROW_NUMBER() OVER (order by sum (sal) desc) SALARY_NUMBER
-
from emp
-
where deptno=10
-
group by ename,empno
-
order by ename,empno;
Ex #3
========== - select ename,empno ,sum(sal) SALARY,
-
rank() over (PARTITION BY ENAME order by sum (sal) desc) RANK,
-
dense_rank() over (PARTITION BY ENAME order by sum (sal) desc) SALARY_DENSE,
-
ROW_NUMBER() OVER (PARTITION BY ENAME order by sum (sal) desc) SALARY_NUMBER
-
from emp
-
group by ename,empno
-
order by ename,empno;
Ex #4
========== - select DEPTNO,empno ,sum(sal) SALARY,
-
rank() over (PARTITION BY DEPTNO order by sum (sal) desc) RANK,
-
dense_rank() over (PARTITION BY DEPTNO order by sum (sal) desc) SALARY_DENSE,
-
ROW_NUMBER() OVER (PARTITION BY DEPTNO order by sum (sal) desc) SALARY_NUMBER
-
from emp
-
WHERE DEPTNO=30
-
group by DEPTNO,empno
-
order by DEPTNO,empno;
Ex #5
========== - SELECT
-
MIN(DEPTNO)
-
KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) BEST,
-
MIN(DEPTNO)
-
KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) WORST
-
FROM EMP
-
GROUP BY DEPTNO;
Ex #6
========== - SELECT
-
MIN(DEPTNO)
-
KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) MIN_BEST,
-
MAX(DEPTNO)
-
KEEP (DENSE_RANK FIRST ORDER BY SUM(SAL) DESC) MAX_BEST,
-
MIN(DEPTNO)
-
KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) MIN_WORST,
-
MAX(DEPTNO)
-
KEEP (DENSE_RANK LAST ORDER BY SUM(SAL) DESC) MAX_WORST
-
FROM EMP
-
GROUP BY DEPTNO;
HANDLING NULLS
----------------------------------- -
select DEPTNO,empno ,sum(sal) SALARY,
-
rank() over (order by sum (sal) desc NULLS LAST) RANK
-
from emp group by DEPTNO,empno;
-
0 8669 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Petre Huile |
last post by:
I have designed a site for a client, but they have hired an internet
marketing person to incrase their search engine ranking and traffic.
He wants to put extra-large fonts on every page which will make the
design looks a bit rediculous. He also said that the big text cannot
be hidden. I am just trying to find a compromise. Here are the
questions:
(1) Is it true that a page with an <H1> tag and very big font size
will make a search...
|
by: ED |
last post by:
I currently have vba code that ranks employees based on their average
job time ordered by their region, zone, and job code. I currently
have vba code that will cycle through a query and ranks each employee
based on their region, zone, job code and avg job time. (See code
below). My problem is that I do not know how to rank the ties. Right
now if two people have the same avg time one will be ranked 3rd and
the other ranked 4th. I would...
|
by: Joseph Bloch |
last post by:
In all the threads on ranking in Access queries, I've not seen
anything that deals with ranking against a subset of the query
results. What I need to do is take the following query results:
Dept Subdept Amount
AAA A1 75
AAA A2 13
AAA A3 45
BBB B1 4
BBB B2 16
|
by: Jeff Boes |
last post by:
I hope this helps someone else ... I had struggled some time ago with
attempts to get a rank of values query to work, but then I gave up and
set it aside. I had another reason to attack it, and in between then and
now I learned how to return "setof" values from a function, as well as
how to construct "dynamic" queries inside a function.
Returning the top 10 values from a query is no big deal:
create table my_table (field1 integer,...
|
by: valglad |
last post by:
Hi,
The question below was posted about 4 years ago and noone was able to
answer it back then. I have virtually the same type of problem so would
appreciate if anyone can help.
Thanks
======================================================================
| |
by: sara |
last post by:
I hope someone can help with this.
Our director wants to have a report that will have the departments
(Retail stores) across the top, stores down the side and the RANKING of
the YTD dept sales within the chain in the cell.
Store/Dept 1 2 3 4
B 8 1 5 2
R 1 3 2 6
(etc.)
|
by: AnndieMac |
last post by:
I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been able to create these summaries easily enough using several sorted queries and a form to select the specific store, or to pull up the regional or national summaries.
The problem is that they want to be able to have one file for each store which...
|
by: Chris |
last post by:
I was wodering if there was a way to rank numbers in a query like this
#'s Rank
100 1
99 2
98 3
98
97 5
96 6
96
|
by: Maciej Gawinecki |
last post by:
Hello,
Recently I've interested in quering XML document streams. They can be
queried in keyword- based manner (non-structural queries), as the
authors of the article "Semantic Search over XML Document
Streams"
have shown.
Do you know any ranking function to ranking triples returned during
search ? I'm not a specialist in this field, but this would require
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |