Standard EAV schema : One column for Entity ID, one for Attribute ID, one for Value ID. Historical EAV schema : Add an additional column(s) for times/date-ranges At run time, certain rows will be excluded. There may be 0, 1, or many rows returned per entity, per attribute. We only want the most recent value for each attribute remainin...
Hi, I run a small golf eclectic with excel. One of the things we have is a points system. I would like to get the 5 highest points scored over the season and have them ranked from 1 (being the highest points scored) to 5. My knowledge of excel "sums" goes only a wee bit further than add and subtract. Thanks! ...
Hi, I have a very big table of measurement data in MySQL and I need to compute the percentile rank for each and every one of these values. Oracle appears to have a function called percent_rank but I can't find anything similar for MySQL. Sure I could just brute-force it in Python which I use anyways to populate the table but I suspect t...
I have a table that has some columns: User, Category, Value And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category. Example: user1 CategoryA 10 user2 CategoryA 11 user3 CategoryA 9 user4 CategoryB 3 user1 CategoryB 11 the query would return: Rank User Category ...
Hi folks, I'm having trouble trying to rank each of our students, by the homegroup they are in. Example fake data. HomeGroup 1. Team RED 2. Team BLUE 3. Team Skeet 4. Team GREEN Students 1. John, Score - 34, Team RED 2. Jill, Score - 87.3, Team RED 3. Fred, Score - 41, Team GREEN 4. Jane, Score 93, Team BLUE ... etc. My output is ...
I've spent quite a lot of time today trying various things, but none of them seem to work. Here's my situation, I'd like to be able to select the rank of a row based on it's ID from a specifically sorted row For example, if my query is something like: SELECT id, name FROM people ORDER BY name ASC with results like: id name 3 A...
Hi Guys, I have a mysql table like below: id name points 1 john 4635 3 tom 7364 4 bob 234 6 harry 9857 I basically want to get an individual user rank without selecting all of the users. I only want to select a single user by id and get the users rank which is determined by the number of ...
Hi I have the following code for searching multiple keywords within a document title which works fine. I want now to have a column that shows the number of keywords that are found in each title row and order in a descendent way. For example if I'm searching for "Maternity leave" I should get something like this: Name -- Keywords found ...
I am trying to write a report (DB2 9.5 on Solaris) to do the following: I have a set of data, let's say it's an order table. I want to run a report which will give me, for each month, the number of orders per customer, and their "rank" that month. The rank would be based on the number of orders. I was playing around with the RANK() OVER...
When defining a calculated property using a formula in NHibernate, what are the implications for when the formula varies its result depending on the query restrictions, especially with regards to query caching? More specifically, consider the following simple C# class: public class Entity { public Entity() { } public virtual in...
I want to rank multiple lists according to their elements how often they appear in each list. Example: list1 = 1,2,3,4 list2 = 4,5,6,7 list3 = 4,1,8,9 result = 4,1,2,3,4,5,6,7,8 (4 is counted three times, 1 two times and the rest once) I've tried the following but i need something more intelligent and something i can do with any ammou...
I have dynamic sql that perform paging and a full text search using CONTAINSTABLE which works fine. Problem is I would like to use FREETEXTTABLE but weight the rank of some colums over others Here is my orginal sql and the ranking weight I would like to integrate (I have changed names for privacy reasons) SELECT * FROM (SELECT TOP 10 T...
I have a table, called Level. id | level | points(minimum) ------------------------- 1 | 1 | 0 2 | 2 | 100 3 | 3 | 200 Let say I have 189 points, how do i check which level the user in? EDIT: Best answer chosen. Now I am comparing the request by adding EXPLAIN before the SELECT query, i have this r...
I have a paginated list of articles that needs to be sorted by rank but when an article has position <> 0 then it must be inserted in that specific position. I thought that I can at least have the articles with the correct position extracted for the current page and then sort them in PHP to show then in the proper position. I want to do ...
Hello, I am in search of some Asp.Net script by which i can get the google pagerank of any url that i supply to it, can anyone guide me in this perspective? ...
Hi, I am trying to figure our a way to calculate rank. Right now it simply takes ratio of wins / losses of each individual entry, so e.g. one won 99 times out of a 100, it has 99% winning rank. BUT if an entry won 1 out of total 1 votes, it will have a 100% winning rank, but definitely it can't be higher that of the one that won 99 time...
I have a table called 'highscores' that looks like this. id udid name score 1 1111 Mike 200 2 3333 Joe 300 3 4444 Billy 50 4 0000 Loser 10 5 DDDD Face 400 Given a specific udid, I want to return the rank of that row by their score...
We need ADT having search and rank features. That is, in addition to the interface of STL map, a function 'int get_rank(key)' is required. Standard implementation of such function requires supporting and updating an extra integer field in every node of self-balanced searching tree (e.g., in black-red tree, used in STL map/set). But it s...
This query gets the top item in each group using the ranking function. I want to reduce the number of inner selects down to two instead of three. I tried using the rank() function in the innermost query, but couldn't get it working along with an aggregate function. Then I couldn't use a where clause on 'itemrank' without wrapping it in...
In which SQL standard was RANK() first introduced? List of SQL standards: SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2008 SQL Rank function: http://en.wikipedia.org/wiki/Select_(SQL)#RANK.28.29_window_function References would be most appreciated. ...