Ranking a created column with multiple criteria

1 
I have a list of multiple columns that I'm wanting to rank. In the example below I am wanting to rank BA in descending order by Month IF team is not blank. 

I tried playing around with Dense_Rank()Over (Partition....then order by ) but ultimately didn't get what I wanted. 

EX: 

Month	    Team AB	 H	BA
8/1/2019	ARI	 50	 9	0.180
8/1/2019	ATL	 45	 11	0.244
7/1/2019	SF	 73	 26	0.356
6/1/2019	SF	 80	20	0.250
8/1/2019		 80	 33	0.413

select 
   DB.Month, 
   DB.Team, 
   DB.AB, 
   DB.H, 
   (DB.[H]/DB.[AB]] as "BA"

FROM [Example].[DBO] as DB

Desired Output:
Month	  Team   AB     H	BA	    BA Rank
8/1/2019 ARI     50  	9	0.180	2
8/1/2019 ATL     45     11	0.244	1
7/1/2019 SF       73	26	0.356	1
6/1/2019 SF       80	20	0.250	1
8/1/2019	       80	33	0.413

Ugh, clearly I can't figure out the formatting of the example but hopefully you get the point..
Thanks for any help,
JAnders

2

hi

i am trying to do this

when you do rank
you need to rank on something ( example: month > 2 or day < 10 )
what is that something

Looking at data .. nothing is straightforward to understand
what to rank by ???

drop create data ... 
use tempdb 
go 

/******************************************************************/
-- drop all tables tempdb 

DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
Exec sp_executesql @sql
/******************************************************************/


/*****************************************************************/
-- create tables tempdb 
-- int varchar(100) date datetime decimal(10,2) 
create table #abc 
(
month1 date,
team varchar(10),
AB int ,
H int,
BA decimal(10,4) 
)
go

create table #desired
(
Month1 date,	  Team varchar(10),   AB int,    H	int ,BA decimal(10,3),BARank int null
)
go 

insert into #desired select '8/1/2019','ARI', 50, 9	,   0.180,	2
insert into #desired select '8/1/2019','ATL', 45, 11,	0.244,	1
insert into #desired select '7/1/2019','SF' , 73, 26,	0.356,	1
insert into #desired select '6/1/2019','SF' , 80, 20,	0.250,	1
insert into #desired select '8/1/2019',' '	, 80, 33,	0.413, null	

/*****************************************************************/


/*****************************************************************/
-- insert data tables tempdb 
-- insert into #abc select 1 
insert into #abc select '8/1/2019','ARI', 50, 9	 , 0.180
insert into #abc select '8/1/2019','ATL', 45, 11 ,	0.244
insert into #abc select '7/1/2019','SF' , 73, 26 ,	0.356
insert into #abc select '6/1/2019','SF' , 80, 20,	0.250
insert into #abc select '8/1/2019','  ' , 80, 33,	0.413
go
/*****************************************************************/


/******************************************************************/
-- select all tables tempdb 

DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' select * from  ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'
Exec sp_executesql @sql
/******************************************************************/
3

Sorry I dont think I was straight forward and used wrong verbiage. The first table is what would be returned with the shown query (below it). What I'm wanting to is create a temp table column "BA Rank" where it ranks BA. column in desc order by Month only if Team name is not blank.

Ex: (incorrect as it's what I'm trying to figure out)
Inserting a new select

select
DB.Month,
DB.Team,
DB.AB,
DB.H,
(DB.[H]/DB.[AB]] as "BA"
--This next select is what I'm trying to figure out/add. How do I exclude from ranking where "TEAM" column is blank
DENSE_RANK ( ) OVER ( PARTITION by
DATE ] ORDER by BA ) As "BA RANK"

FROM [Example].[DBO] as DB

Hopefully that makes sense, sorry I'm pretty new to SQL, @harishgg1 it looks like you were creating a whole new table vs just adding a new temp column in the query?

Sorry for poorly written question.

5

Hi JAnders

I understand what you are saying..

It's my bed Time

No access to my computer

Please give me some time

I will have it done for you
First thing my morning tomorrow

:+1::+1::slightly_smiling_face::slightly_smiling_face:

1 Like
6

Hi JAnders

I tried to do this .. please see if its ok ??

drop create data ... 
/* ------------------------------------------------------------   */
-- select database tempdb 

USE TEMPDB
GO 
/* ------------------------------------------------------------   */


/******************************************************************/
-- drop all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' Drop table ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/******************************************************************/


/*****************************************************************/
-- create tables tempdb 
-- int varchar(100) date datetime decimal(10,2) 
create table #abc 
(
month1 date,
team varchar(10),
AB int ,
H int,
BA decimal(10,4) 
)
go

/*****************************************************************/

/*****************************************************************/
-- insert data tables tempdb 
-- insert into #abc select 1 
insert into #abc select '8/1/2019','ARI', 50, 9	 , 0.180
insert into #abc select '8/1/2019','ATL', 45, 11 ,	0.244
insert into #abc select '7/1/2019','SF' , 73, 26 ,	0.356
insert into #abc select '6/1/2019','SF' , 80, 20,	0.250
insert into #abc select '8/1/2019','  ' , 80, 33,	0.413
go
/*****************************************************************/


/*****************************************************************/


/* ------------------------------------------------------------   */
-- select all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' SELECT * FROM  ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/* ------------------------------------------------------------   */
SQL ..... 
/******************************************************************/
-- Query Solution 

;WITH cte 
     AS (SELECT db.month1, 
                db.team, 
                db.ab, 
                db.h, 
                db.h / ( db.[ab] * 1.0 ) AS BA 
         FROM   (SELECT * 
                 FROM   #abc 
                 WHERE  team NOT LIKE '% %') DB) 
SELECT *, 
       Dense_rank () 
         OVER ( 
           ORDER BY Month(month1) DESC ) AS "BA RANK" 
FROM   cte 

go

image
image.png683×625 11.6 KB