sql,sql-server,delete,conditional,rank

From the looks of it, you want to delete duplicate entries for Name and Product leaving the newest one. You can simplify your query with this: SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Name, Product ORDER BY [Date] DESC) FROM Config You can then put it in a CTE and...

Here's a base-R solution: uv <- unique(df$Value) merge(df,data.frame(uv,r=rank(uv)),by.x="Value",by.y="uv") which gives Value Name r 1 50 Bob 1 2 50 Todd 1 3 51 John 2 4 55 Mary 3 5 55 Tom 3 6 56 Linda 4 This is memory inefficient and has the side-effect of resorting your data. You...

If I understand your question then I think you need to put your ROW_NUMBER expression outside the union as follows: SELECT ROW_NUMBER() OVER ( ORDER BY T.SupplierItemCode) AS LineNumber, T.EAN, T.CustomsCode, T.SupplierItemCode, T.ItemDescription, T.ItemNote, T.VATType, T.PackageType, T.OrderQuantity, T.UnitOfMeasure, T.OrderedUnitNetPrice FROM ( SELECT NULL AS EAN, NULL AS CustomsCode, ProductId AS...

sql,sql-server,group,rank,partition

This is a bit tricky. You can get the grouping for the ids with a trick -- a difference of row numbers. Then you need to get the minimum id for each group, to ensure that the final rank is in the right order. Then you can use then you...

The following does what you want for all films: select r.*, (case when row_number() over (partition by filmname order by date) = 1 then 1 else 0 end) as IsWatchedFirstAndGladiator from results r; For just Gladiator: select r.*, (case when filmname = 'Gladiator' and row_number() over (partition by filmname order...

Let's come up with a more precise definition of what you are after: Given a list L of numbers, create a list M that contains the valid indices of L, such that M[i] is the index at which the ith element of L occurs, when sorted. Present M as if...

You can try library(data.table) setDT(df1)[order(-Scored.Probabilities), rank:= 1:.N, Interest][ order(Interest), Set := .GRP, Interest][order(Interest, rank)] # Interest Age Gender Scored.Probabilities rank Set #1: AL008 18-24 male 0.211 1 1 #2: AL008 35-44 female 0.102 2 1 #3: AL008 25-34 female 0.002 3 1 #4: AL024 13-17 male 0.102 1 2 #5:...

You can add more conditions to the comparison: SELECT uo.*, (SELECT COUNT(*) FROM photo_list ui WHERE ui.total_points > uo.total_points OR ui.total_points = uo.total_points AND ui.submitted_date >= uo.submitted_date ) AS rank FROM photo_list uo I'm not sure what order you want the ranking in for the submitted date, but either >=...

for your question How difficult is it also to have "T" before any ties?... its not difficult, but I can't do it on my end because I dont have any data for ties.. (1. if you want to edit the table to add "T") UPDATE leaderboard_A SET tie = Concat('T',...

Would this work for you? =MAX(0,RANK(O22,$O$9:$O$58, 1)-COUNTIF(O:O,0)) It'll prevent the "0s" from going negative, and leave them displayed as 0's. You could leave the MAX off, however, they'll display as negatives then. Either way .. Alternately, if you can not show "0", but leave the cell blank, it won't consider...

Here's one approach: lapply(list, function(x) { t(apply(x, 1, function(y) { y[!y %in% tail(sort(y), 2)] <- 0 y })) }) ## $`2010` ## 1 2 3 4 ## 1 0 0 5 6 ## 2 5 0 9 5 ## 3 0 0 0 0 ## 4 10 10 10 0...

Here is a simple Schwartzian transform based on the assumption that all actual values are smaller than 123456789. awk '{ printf "%s\t%s", ($2 || 123456789), $0 }' file | sort -n | cut -f2- >output ...

you need helper nodes to get the correct rank for struct1. digraph G { ranksep=.75; nodesep = 1.5; node[fontsize=16,fixedsize=false,width=0.7,shape=rectangle]; edge[fontsize=16]; compound=true subgraph cluster2 { rank="max" node [shape=record,color=white]; style=filled; color=lightgrey; label = "ISRs"; struct1 [shape = record, color=white, label="{<f1> Slow ISR | <f2> Fast ISR }"]; } subgraph cluster0 { node...

If i am understanding this correctly I am thinking you want this. Also this provides sample code, if the first assumptions are wrong please comment. create table #apples (product varchar(20) ,orders varchar(20) ,qty int ) insert into #apples values ('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','josh',1) ,('apple','josh',1) ,('apple','jacob',1) ,('apple','jennifer',1) Go With...

Kind of a strange requirement, but here's the solution: select date, number, type, dense_rank() over(order by date, type asc, rank asc) rank2 from ( select row_number() over (partition by date, number, type order by date asc,type desc) rank, * from testTable ) a order by date, number desc, rank2 asc...

r,data.frame,rank,quantile,split-apply-combine

The idiomatic way to do this kind of thing in R would be to use a combination of split and lapply. You're halfway there with your use of lapply; you just need to use split as well. lapply(split(data, data$v1), function(df) { cutoff <- quantile(df$v2, c(0.8, 0.9)) top_pct <- ifelse(df$v2 >...

Then you want to use a window function to calculate the total volume. I think the logic is: select sum(volume) / sum(sum(volume)) over (partition by EffectiveDate) If volume is an integer, you should convert it to a non-integer number. Otherwise, the division will return an integer -- and be either...

sql,sql-server,group-by,rank,row-number

I am assuming that you want to get the TOP N result for the sitecode partition with total_cpu_time as order. You can put the Where clause inside if you need to filter data afterwords just filter in inner query. You can use the Row_Number() instead of the rank(), SELECT rs.RunID,rs.SiteCode,rs.procedure_name,rs.total_cpu_time,...

If I understand correctly, you want to assign rank based on the order of the timestamp. The older the timestamp, the higher is the rank in the queue. I would suggest ANALYTIC function. To keep it simple, you could use ROW_NUMBER. For example, SELECT code, cid, ROW_NUMBER() OVER(ORDER BY timestamp_column)...

I think this will produce what you want: SELECT r.*, row_number() over (partition by (case when colb is null and colc is null and cola is not null then 1 else 0 end), (case when colb is null and colc is null and cola is not null then NULL else...

sql,sql-server,rank,case-statement,rownumber

You can use SUM OVER() and ROW_NUMBER() Based on your result, I order the first criteria by # of Contracts DESC. SQL Fiddle WITH Cte AS( SELECT *, ZeroRatio = SUM(CASE WHEN Ratio = 0 THEN 1 ELSE 0 END) OVER() FROM TestData ), CteRN AS( SELECT *, RN =...

c++,type-conversion,implicit-conversion,data-type-conversion,rank

The 4.13 section says that Every integer type has an integer conversion rank defined as follows: — No two signed integer types other than char and signed char (if char is signed) shall have the same rank, even if they have the same representation. — The rank of a signed...

Had a similar issue, my answer was sorting on groups and the relevant ranked variable(s) in order to then use row_number() when using group_by. # Sample dataset df <- data.frame(group=rep(c("GROUP 1", "GROUP 2"),10), value=as.integer(rnorm(20, mean=1000, sd=500))) require(dplyr) print.data.frame(df[0:10,]) group value 1 GROUP 1 1273 2 GROUP 2 1261 3 GROUP...

r,graphviz,dot,rank,directed-acyclic-graphs

Here's my answer. Suggestions welcome. gR is an object of formal class GRAPHNEL gx <- layoutGraph(gR) #can also add node attributes, sublists here x <- [email protected]@nodes$nodeX y <- [email protected]@nodes$nodeY #x and y are named vectors with node names and x / y coordinates #to get the names of the nodes...

Usually when working with tabular data in Python, the pandas library is a good tool to reach for. There are lots of ways to do what you want, IIUC, but here's one which shouldn't be too hard to follow. It's mostly to give you a sense for the kinds of...

I do not share your aversion to datetime objects, which makes this all much simpler: dataf$ts <- strptime(as.character(dataf$Timestamp),'%m/%d/%Y %H:%M') dataf <- dataf[order(dataf$ts),] dataf$ts_rank <- rank(dataf$ts,ties.method = "min") dataf ## hours mins date time Timestamp Actor ts ts_rank ## 19 9 04 4/28/2014 9:04 4/28/2014 9:04 7 2014-04-28 09:04:00 1 ##...

There are a few related ways to do this, most involving SUMPRODUCT. If you don't like the solution below and would like to research other ways/explanations, try searching for "rankif". The function looks up the Class and Value columns and, for every value in those columns, returns a TRUE or...

what about creating a new vector with the value appended and rank that ? so creating a loop that for each element of vectora does: rankVector=rank(append(c(a),c1)) And then retrieve the first value of rankVector. The full loop would look like: for (i in seq_along(a) ){ rankVector[i]=rank(append(c(a[i]),c1)) } Cheers !...

Try apply(-data, 1, rank, ties.method='first') and compare with apply(data, 1, rank, ties.method='first') For your specific example v1 <- c(2,1,3,5) rank(v1) #[1] 2 1 3 4 rank(-v1) #[1] 3 4 2 1 data set.seed(24) data <- as.data.frame(matrix(sample(1:20, 4*20, replace=TRUE), ncol=4)) ...

graphviz,overlap,rank,dot,subgraph

Answers from both ssteve and emden were very useful and have been upvoted. (thank you both!) I am incorporating input from both and answering this question in its more complex version. I still do not feel like the solution is perfect, but it is the best I can generate so...

declare @test table ( CustNumber int ) insert into @test values (25122134), (25122134), (25122134), (25122136), (25122136) select * , // each CustNumber in partition has the same rank, but different row_number case when (row_number() over (partition by CustNumber order by CustNumber)) = 1 then 1 else 0 end as [Unique]...

sql-server,tsql,partitioning,rank

You can use the following query: SELECT merge_tx_id, merge_from_id, merge_to_id FROM ( SELECT merge_tx_id, merge_from_id, merge_to_id, ROW_NUMBER() OVER (PARTITION BY x.merge1, y.merge2 ORDER BY merge_tx_id DESC) AS rn FROM merge_grp CROSS APPLY (SELECT CASE WHEN merge_from_id < merge_to_id THEN merge_from_id ELSE merge_to_id END AS merge1) AS x CROSS APPLY (SELECT...

On the given input as you describe in your Question. You can use this as a solution: -- Create demo data CREATE TABLE #a(date date, segment int, total int) INSERT INTO #a(date, segment, total) VALUES (N'04/11/2015',1,3), (N'04/12/2015',3,2), (N'04/13/2015',5,1) CREATE TABLE #b(date date, sequence int, segment int) INSERT INTO #b(date, sequence)...

You can do this with EXISTS: SELECT * FROM Table1 a WHERE EXISTS (SELECT 1 FROM Table1 b WHERE a.ID = b.ID AND b.Date < a.Date ) AND EXISTS (SELECT 1 FROM Table1 b WHERE a.ID = b.ID AND b.Date > a.Date ) Demo: SQL Fiddle ...

Here's one way to do it in Pandas-way You could groupby on Auction_ID and take rank() on Bid_Price with ascending=False In [68]: df['Auction_Rank'] = df.groupby('Auction_ID')['Bid_Price'].rank(ascending=False) In [69]: df Out[69]: Auction_ID Bid_Price Auction_Rank 0 123 9 1 1 123 7 2 2 123 6 3 3 123 2 4 4 124...

df <- data.frame(sampling_date=c('1/1/00','5/1/00','9/1/00','13/1/00','17/1/00', '21/1/00','25/1/00','29/1/00','1/2/00','5/2/00', '9/2/00','13/2/00','17/2/00','21/2/00','25/2/00','28/2/00'), month=c(1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2), breeder=c(0,10,50,100,30,20,12,3,10,20,50,80,50,51,30,10), gregorian_days=c(1,5,9,13,17,21,25,29,33,37,41,45,49,53,57,61)) df$sampling_date <- as.Date(df$sampling_date,'%d/%m/%y') df$rank <- do.call(c, by(df, df$month, function(x) { breeding <- x$breeder>=20 first <-...

Using the MODEL clause Behold for you are pushing your requirements beyond the limits of what is easy to express in "ordinary" SQL. But luckily, you're using Oracle, which features the MODEL clause, a device whose mystery is only exceeded by its power (excellent whitepaper here). You shall write: SELECT...

You need to convert you matrix to a rank-revealing decomposition. For instance FullPivLU. If you have a matrix3f it looks like this : FullPivLU<Matrix3f> lu_decomp(your_matrix); auto rank = lu_decomp.rank(); Edit decompose the matrix is the most common way to get the rank. Although, LU is not the most reliable way...

I have a data.table-based solution: library(data.table) setDT(d)[,consistent:=rank < 3 & c(NA,rank[-.N])<3 & c(NA,NA,rank[-c(.N-1,.N)])<3,name] d # name date rank consistent # 1: a 2014-01-01 3 FALSE # 2: c 2014-01-01 2 NA # 3: b 2014-01-01 1 NA # 4: c 2014-01-05 3 FALSE # 5: b 2014-01-05 2 NA #...

excel,function,if-statement,rank

Assuming 100 rows of data (change as required) and with required date range defined in E2 (start date) and E3 (end date), use this "array formula" in G2 for numbers: =LARGE(IF(C$2:C$100>=E$2,IF(C$2:C$100<=E$3,B$2:B$100)),ROWS(G$2:G2)) Confirm with CTRL+SHIFT+ENTER and copy down to G11 then in F2 for text: =INDEX(A$2:A$100,SMALL(IF(B$2:B$100=G2,IF(C$2:C$100>=E$2,IF(C$2:C$100<=E$3,ROW(B$2:B$100)-ROW(B$2)+1))),COUNTIF(G$2:G2,G2))) Confirm with CTRL+SHIFT+ENTER and copy...