• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

SQL Query dense rank

How can I display 1 and 2, 3.. ranking for Items that have a ranking of 2 or more
0
Leo Torres
Asked:
Leo Torres
  • 11
  • 6
  • 4
  • +2
3 Solutions
 
tigin44Commented:
can you provide your table definition..
0
 
tigin44Commented:
a query like this may guide you
SELECT rownum, rankingColumn, ....
FROM(   
  SELECT rankingColumn, ..., ROW_NUMBER() OVER (PARTITION BY rankingColumn ORDER BY someColumn) as rownum
  FROM yourTable y
	inner join (SELECT id FROM yourTable group by rankingColumn HAVING COUNT(*) > 1) D ON Y.id = D.id
	)
ORDER BY rankingColumn,	rownum

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Sorry here is the link

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25031492.html

let me know if you still need more info
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Leo TorresSQL DeveloperAuthor Commented:
This was the query I was using
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail
where Left(ItemNumber,1) not in ('/','*')
) a
where ranking > 1

Open in new window

0
 
tigin44Commented:
TRY TH0S
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail d
		INNER JOIN (SELECT ItemNumber, Product_Number 
					FROM AccessData.dbo.Product_Detail 
					GROUP BY ItemNumber, Product_Number
					HAVING COUNT(*) > 1) A ON A.ItemNumber = D.ItemNumber, A.Product_Number = D.Product_Number
where Left(ItemNumber,1) not in ('/','*')
) a
where ranking > 1

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
try adding another ranking to the outer query like this:
select SecondRanking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),* 
from ( 
select FirstRanking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),* 
from AccessData.dbo.Product_Detail 
where Left(ItemNumber,1) not in ('/','*') 
) a 
where FirstRanking > 1

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
OK tigin44 your gave me this error

Server Msg: 102, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near '='.


CGLuttrell Your ran but it displays the 2 rankings in 1 row I need to display row by ro so i Can compare the differences
below is a sample of what i got

I need to display so i can see the differce between rows

1
2
3

Thank you both!!




SecondRanking	FirstRanking	ID	ItemNumber
1	               2	5884	24-16611
1	               2	5710	24-17420
1	               2	5932	24-18808
1	               2	6096	24-55147
1	               2	7308	25-404626
1	               2	7447	25-419638
1	               2	6185	35-13170
1	               2	6208	35-15088
1                      2	5719	35-31624
1	               2	5723	40-43950
1	               2	5724	43-05800
1	               2	6641	43-05801
1	               2	6642	43-05802
1	               2	6643	43-05803
1	               2	5728	43-05900
1	               2	5729	43-05901
1	               2	5730	43-05903
1	               2	6031	43-08345
1	               2	6826	45-29830
1	               3	7362	45-29840
2	               2	6828	45-29840
1	               2	6841	45-30450
1	               2	6844	45-30530

Open in new window

0
 
tigin44Commented:
a typo error try it now
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail d
                INNER JOIN (SELECT ItemNumber, Product_Number 
                                        FROM AccessData.dbo.Product_Detail 
                                        GROUP BY ItemNumber, Product_Number
                                        HAVING COUNT(*) > 1) A ON A.ItemNumber = D.ItemNumber  AND A.Product_Number = D.Product_Number
where Left(ItemNumber,1) not in ('/','*')
) a
where ranking > 1

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
More errors from query above


Server Msg: 4104, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
The multi-part identifier "D.ItemNumber" could not be bound.
Server Msg: 4104, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
The multi-part identifier "D.Product_Number" could not be bound.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'Product_Number'.
Server Msg: 8156, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
The column 'ItemNumber' was specified multiple times for 'a'.
0
 
Chris LuttrellSenior Database ArchitectCommented:
>>>  CGLuttrell Your ran but it displays the 2 rankings in 1 row I need to display row by ro so i Can compare the differences
The 2 rankings are from the 2 times we used it, you probably do not need the second one.  also, we may want to chang the order by in the second ranking statement.
Try something like this, I think it is working because the first time you had more than 1 entry for the same ItemNumber the results were
1                      3        7362    45-29840
2                      2        6828    45-29840

Select Ranking = dense_rank() over(partition by ItemNumber, Product_Number order by FirstRanking), ID, ItemNumber
from (  
select FirstRanking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*  
from AccessData.dbo.Product_Detail  
where Left(ItemNumber,1) not in ('/','*')  
) a  
where FirstRanking > 1

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
oops, I said "do not need the second one" but I meant to say probably do not need the first one in your output, it was just for the >1 exclusion
0
 
tigin44Commented:
h1mm
select * from (
select ranking = dense_rank() over(partition by d.ItemNumber, d.Product_Number order by newid()),*
from AccessData.dbo.Product_Detail d
        INNER JOIN (SELECT ItemNumber, Product_Number 
                    FROM AccessData.dbo.Product_Detail 
                    GROUP BY ItemNumber, Product_Number
                    HAVING COUNT(*) > 1) A ON A.ItemNumber = D.ItemNumber  AND A.Product_Number = D.Product_Number
where Left(d.ItemNumber,1) not in ('/','*')
) a
where ranking > 1

Open in new window

0
 
SharathData EngineerCommented:
You can use row_number instead of dense_rank. Can you post your sample data and expected result?
0
 
lofCommented:
wait a second, shouldn't you be using row_number rather than rank?

I am a bit confused with your question.

You could do

select * from (
select ranking = row_number() over(partition by itemnumber, productnumber, cdate order by itemnumber, productnumber, cdate) rownum, *
from Pricing
) numberedRows
where rownum >2

and if you want to have numbers for duplicates shifted (so that you see it 1,2,3 rather than 2,3,4 in first select you should put

select rownum-1, * from......
0
 
Leo TorresSQL DeveloperAuthor Commented:
If I try without little a at the end then i get this error

Server Msg: 156, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near the keyword 'where'.

this has to be the outer where

select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail d
                INNER JOIN (SELECT ItemNumber, Product_Number 
                                        FROM AccessData.dbo.Product_Detail 
                                        GROUP BY ItemNumber, Product_Number
                                        HAVING COUNT(*) > 1) A ON A.ItemNumber = D.ItemNumber  AND A.Product_Number = D.Product_Number
where Left(ItemNumber,1) not in ('/','*')
) 
where ranking > 1

Open in new window

0
 
tigin44Commented:
this should fix
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail d
                INNER JOIN (SELECT ItemNumber, Product_Number 
                                        FROM AccessData.dbo.Product_Detail 
                                        GROUP BY ItemNumber, Product_Number
                                        HAVING COUNT(*) > 1) A ON A.ItemNumber = D.ItemNumber  AND A.Product_Number = D.Product_Number
where Left(ItemNumber,1) not in ('/','*')
) X
where ranking > 1

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
OK let me re state my question with more details

The query below results in the file below

I have included a file below..

That works fine...

My mission is I need to display rank 1 as a row so I can compare the two rows and see why are they different ...
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail
where Left(ItemNumber,1) not in ('/','*')
) a
where ranking > 1

Open in new window

SAmplerank.xlsx
0
 
Leo TorresSQL DeveloperAuthor Commented:
to be more clear i guess I want to see all rows that a rank higher than 1 but also display 1,2,3


another way of saying it would be i Dont and to see rows that only have a rank of 1 ... Display the rank of one if you have items that have a rank higher than 1

... Hope that helps..
0
 
SharathData EngineerCommented:
Can you post your expected result?
0
 
Leo TorresSQL DeveloperAuthor Commented:
Upon request I have created a file to show what it should look like... Please disregard the data is incorrect but look an the first column and how it is organized in order for those items that have more than more ranking and it displays all...
SAmplerank2.xlsx
0
 
SharathData EngineerCommented:
Please see the result of this query.
;with cte_1 as (
select * from (
select ranking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*
from AccessData.dbo.Product_Detail
where Left(ItemNumber,1) not in ('/','*')
) a
where ranking > 1),
cte_2 as (
select *,row_number() over (partition by ItemNumber order by (select 1)) as rn
  from cte_1)
select * from cte_2

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:

lof I get this from your Query from post ID:26221580
Server Msg: 102, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near 'rownum'.

tigin44 your post ID:26223836Author: Produced these errors

Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'ItemNumber'.
Server Msg: 209, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Ambiguous column name 'Product_Number'.
Server Msg: 8156, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
The column 'ItemNumber' was specified multiple times for 'X'.



CGLuttrell yours returns data but it only returns rank i need both 1 and 2 and 3 if applicable

Select Ranking = dense_rank() over(partition by ItemNumber, Product_Number order by FirstRanking), ID, ItemNumber
from (  
select FirstRanking = dense_rank() over(partition by ItemNumber, Product_Number order by newid()),*  
from AccessData.dbo.Product_Detail  
where Left(ItemNumber,1) not in ('/','*')  
) a  
where FirstRanking > 1


ID:26231383 Sharath_123

this query does the same thing gives me the same 23 results as before...
0
 
lofCommented:
that's what happens when you change other people queries in a rush.

select * from (
select row_number() over(partition by itemnumber, productnumber, cdate order by itemnumber, productnumber, cdate) rownum, *
from Pricing
) numberedRows
where rownum >2
0
 
SharathData EngineerCommented:
<< ID:26231383 Sharath_123

this query does the same thing gives me the same 23 results as before... >>

My query will give you the same records but with an additional column as rn which has the values what you are loking for.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Lof your query now runs but I get no results..

Yes its does give me are rn but it does not bring in the all rows I need

If you see the file I posted it shows the entire row twice for each itemNumber because there is a difference between 1 and 2 i want to see both rows so I can see what those differences are..

Sorry if i am not being clear on this ..

Thank you all for ll your effort and help!!
0
 
Leo TorresSQL DeveloperAuthor Commented:
Queries Helped had to do it manually

Thank to all
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 11
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now