FAQ Database Discussion Community


Creating unique identifier column(1 or zero) Rank () SQL SERVER

sql-server,rank
I am trying to create a column in SQL SERVER that shows 1 OR 0(zero). I have a column of customer numbers that appear more than once. At the first hit on a unique non-repeated customer number it should show one and if it is repeated then 0(zero). How can...

Selecting SQL TOP N rows from group, row_number, rank not working

sql,sql-server,group-by,rank,row-number
In SQL Server, I have one table with following fields. I would like to get Top N rows from each group. I have tried following sql query with Rank() and Row_number() but rank and row numbers are not in sequence so not able to limit the rows. Here is SQL...

Creating a queue from timestamps SQL

sql,oracle,timestamp,rank
Hello I have a problem trying to create a view symbolizing a queue. It's about students in a waitinglist trying to register for courses. I have a table "Waits" with the attributes queue# TIMESTAMP, stundentID STRING, courseCode STRING, From this I am trying to create a View which symbolize a...

Rank periods with different conditions in the same dataframe

r,rank
I would like to rank the rows of a dataframe with different criteria according some conditions. I have a dataframe with the following data: sampling date, month of the smpling date, percentage of breeding individuals in the population and gregorian days. I would like to rank the rows in a...

What does rank mean in relation to type conversion?

c++,type-conversion,implicit-conversion,data-type-conversion,rank
From C++11 standard (draft n3337) §5/9: — If both operands have the same type, no further conversion is needed. — Otherwise, if both operands have signed integer types or both have unsigned integer types, the operand with the type of lesser integer conversion rank shall be converted to the type...

How do I find the rank of a node in rgraphviz?

r,graphviz,dot,rank,directed-acyclic-graphs
I have a directed acyclic graph in rgraphviz, it's going to be rendered as a DOT graph. I want to control the appearance of nodes based on its rank. How do I find the rank of nodes?

Find top deciles from dataframe by group

r,data.frame,rank,quantile,split-apply-combine
I am attempting to create new variables using a function and lapply rather than working right in the data with loops. I used to use Stata and would have solved this problem with a method similar to that discussed here. Since naming variables programmatically is so difficult or at least...

Using Rank or OVER() to create 1 or zero column SQL SERVER

sql,sql-server,rank
This is what my result data looks like at the moment. Below is a sample of my data. Cust_Nr Date FilmName 157649306 20150430 Gladiator 158470722 20150504 Gladiator 158467945 20150504 Out Of The Furnace 158470531 20150504 FilmA 157649306 20150510 Gladiator 158470722 20150515 Gladiator as we can see in the table, customer...

Alignment issue with two clusters using Graphviz and Dot

graphviz,dot,rank
I'm trying to get the following dot file to output two subgraphs. I want the bLoop node in cluster0 to align with the ISR struct in cluster 2. I'm using an invisible node to do this now, but with the unintended consequence of lot of gray space left in cluster0....

express percent as a percentage of the total volume rather than ranked volume

sql,sql-server,rank
Given this query: select [Code], [EffectiveDate], [Volume] = Sum(Volume), [VolumeRank] = RANK() OVER (PARTITION BY EffectiveDate ORDER BY SUM(Volume) DESC), [VolumePercent] = PERCENT_RANK() OVER (PARTITION BY EffectiveDate ORDER BY SUM(Volume) ASC) from CodeTable where EffectiveDate between @start and @end group by DerivativeType, MemberCode, EffectiveDate Which produces these results X 2014-05-02...

How to filter out the first and last entry from a table using RANK?

sql,group-by,rank
I've this data: Id Date Value 'a' 2000 55 'a' 2001 3 'a' 2012 2 'a' 2014 5 'b' 1999 10 'b' 2014 110 'b' 2015 8 'c' 2011 4 'c' 2012 33 I want to filter out the first and the last value (when the table is sorted on...

DELETE FROM Table WHERE 'RANK' > 1

sql,sql-server,delete,conditional,rank
I am new to the RANK and CTE queries. The following is my query: SELECT * ,ROW_NUMBER() OVER(PARTITION BY name, Product ORDER BY Date desc) AS 'RANK' FROM Config WHERE Name IN (SELECT name FROM Config GROUP BY Name ,Product ,Amount ,Date HAVING COUNT(name) >1) AND Product IN (SELECT Product...

R: Increment Rank when the column group changes

r,data.frame,rank
Newbie to R, I've tried Googling but I'm failing find a solution. Here's my data frame: Name Value Bob 50 Mary 55 John 51 Todd 50 Linda 56 Tom 55 So I've sorted it but what I need to add a rank column, so it looks like this: Name Value...

r rank value in vector

r,vector,ranking,rank
I have a sequence of numbers and vectors that should be used to evaluate each of the sequence of numbers according to their ranks. Example a <- c(20, 29, 22) b <- c(21, 22, 27) data <- data.frame(cbind(a,b)) c1 <- c(12,23,34,21,33,22,35,3,5,6,7,29,49) c2 <- c(12,23,34,21,33,22,35,3,5,6,7,29,49) How can I check the rank...

Keep the 2 highest values of each row in a list of matrices r

r,matrix,rank
Suppose I have a list of matrices: $`2010` 1 2 3 4 1 0 3 5 6 2 5 1 9 5 3 0 0 0 0 4 10 10 10 0 $`2011` 1 2 3 4 1 0 2 3 6 2 5 0 3 1 3 2 4...

Ranking with subsets

excel,excel-formula,rank
I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two...

Sum and Ranking

excel,if-statement,rank
I currently have each row providing a sum for "Total Score". Then, I have the "Placing" column to rank. I am having an issue when there is a "0" for a score, my rank will show "1". A simple solution is to delete my sum for those rows, but I...

Pandas rank by column value [duplicate]

pandas,dataframes,rank
This question already has an answer here: python pandas rank by column 1 answer I have a dataframe that has auction IDs and bid prices. The dataframe is sorted by auction id (ascending) and bid price (descending): Auction_ID Bid_Price 123 9 123 7 123 6 123 2 124 3...

Apply rank using RowNumber based on multiple conditions

sql,sql-server,rank,case-statement,rownumber
Looking for some suggestions here on a project that I am currently working on. I have a table that consists of Vehicle Name Number of Contracts Sale Amount Ratio I would like to add a Row Number to the table based on certain criteria. IF there are 5 or more...

How to rank values in different columns based on a criteria in Python (transferring Excel calculations to Python)?

python,excel,rank
I have a workbook in Excel with a bunch of calculations, but the file is getting too large and the calculations are taking too long to finish in Excel so I am trying to move the file to another program (preferably Python) to handle the calculations. I only have basic...

R: choose different raw and separate to new ranked set

r,rank
I have a dataset which looks like: Interest Age Gender Scored.Probabilities AL008 18-24 male 0.211 AL024 25-34 male 0.022 AL008 35-44 female 0.102 AL008 25-34 female 0.002 AL024 13-17 male 0.102 AL035 35-44 female 0.027 AL024 35-44 female 0.051 AL024 55-64 male 0.025 AL024 35-44 male 0.016 AL034 45-54 male...

SQL Recursive Query - Sum Rows with Lesser Rank

sql-server,rank
Using MS SQL - 2012 I am trying to run a SQL Query that ranks rows by product id's. Then calculates a sum that represents the rows ranked less than or equal to it. I set up a formula to do this and it works but is slow and resource...

In Excel: Return nth largest value in a group of numbers given constraints

excel,function,if-statement,rank
I was wondering if anyone could help with the following Excel question: If I have three columns, Column A containing tweet texts, Column B containing the number of impressions that tweet has generated, and Column C containing the date of the tweet - I would like to output the following...