SQL Query dense rank

can you provide your table definition..

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

Select allOpen in new window

Sorry here is the link

https://www.experts-exchange.com/questions/25031492/SQL-Query.html

let me know if you still need more info

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

have a look at this excelent mwvisa1 article

https://www.experts-exchange.com/articles/Database/Miscellaneous/Analytical-SQL-Where-do-you-rank.html?sfQueryTermInfo=1+30+do+how+rank+you

it is all about ranking

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

Select allOpen in new window

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

Select allOpen in new window

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'.

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

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

Select allOpen in new window

You can use row_number instead of dense_rank. Can you post your sample data and expected result?

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

SAmplerank.xlsx

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..

Can you post your expected result?

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

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

Select allOpen in new window

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...

<< 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.

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!!

Queries Helped had to do it manually

Thank to all