Counting And Ranking Within Groups
(commas are in place to show data seperation and are not needed in the result set)
Name Won
Julio, 2
julio, 3
julio, 0
Ron, 4
Ron, 2
and the results should be based upon wins sorted descending
Name Won Rank
Julio, 3, 1
julio, 2, 2
julio, 0, 3
Ron, 4, 1
Ron, 2, 2
View Complete Forum Thread with Replies
Related Forum Messages:
Groups
I have: col1col2 item13 item15 item27 item23 item14 item34 I'm looking for the result: col1col2 item15 item27 item34 --the highest col2 value for data paired with col1 --no duplicates in col1 --order does not matter for either col1 or col2 in the result The SQL I have now is: select col1, col2 from table1 group by col1 I'm not sure how to force it to group col1 using the highest value from col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if this is relevant, but table1 is made by joining two other tables together (one of which is a heap).
View Replies !
Group Of Groups
Basically, I have a simple group query... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` The query returns the number of records for each student. What I want is a query that produces a count of the number of students with a distinct number of records, that is, I want to group by COUNT(*) and return the COUNT(*) of the new group. My nieve atmysqlt... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` GROUP BY COUNT(*)
View Replies !
Groups For Members
My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list. Would this be a good way to do it?.
View Replies !
Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column. I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.
View Replies !
Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league. for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20. I want to show on each individual team page their stat, with the rtanking for the league.
View Replies !
Ranking Users
i'm trying to rank the users in our site. table contains: - userid - userrates and with thousands of records. member with the highest rate goes #1, next is #2 and so on. i want the query to be like: Code: <something goes here> WHERE userid = '$userid' order by userrates desc from the code above it will show the rank of this user. then the next user logsin and it shows again his/her ranking. hope someone understands this any input is appreaciated
View Replies !
Ranking Scores
I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table. the link below shows an example image of what i need to happen.
View Replies !
Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts? And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.
View Replies !
Order By Giving Two Groups
I have a new database table which contains name and url field. When I order by name I get two groups, the first being those without an entry in the url field, the second with an entry in the url field, each grou seperately ordered correctly. In a possibly related problem with the same table displaying in a web browser through php, there are again two groups diplaying when ordered by name. If a member of the first group is displayed and resubmitted without change, it then appears in the second group. I tried doing an export / import to refresh the data, but it didn't help. Any ideas for causes and or solutions?
View Replies !
Two Servers On Windows And My.cnf/my.ini Groups
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
View Replies !
Return Groups For Each Month
I would like to return monthly reports with a single query.For example: January 15 Purchases 5 Refunds 6 Exchanges February 20 Purchases 4 Refunds 2 Exchanges The above data has about 60 records. The query has to group by month and then again by transaction type. Here is what the record would look like TransactionID - primary key TransactionDate - Date Transactiontype - integer
View Replies !
Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this: [news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid] One entry in 'news' can be attached to many different categories via 'newscategory'. What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,
View Replies !
Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table. For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank. An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method. Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.
View Replies !
Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved. Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on? For example if I had a basic table with fields of name and points. You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name. How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated... Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.
View Replies !
Sort And Give Ranking
let say i've have these following table. tblA name | marks ----------------- robert | 39 johnny | 78 bruce | 23 elena | 56 halim | 23 formula, if same marks (see bruce and halim), sort them by name how to query? expected output showing as below: no | name | marks ---------------------------- 1 | johnny | 78 2 | elena | 56 3 | robert | 39 4 | bruce | 23 5 | halim | 23
View Replies !
Ranking Rows With Filtered Data
What I am trying to accomplish is ranking/sorting rows in a database filtered by one of the columns of data. I can do this right now, but I have to run two queries (shown below) for each "filter" set I want to do. I'd like to know if there is a single query that can do this. Here's the table setup (pared down) and some example rows to illustrate Code: ....
View Replies !
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14 or 3.23 SQL to get student grade ranking where tied grade have the same rank. I used to set it through MS Access 2002 and use this kind of query: SELECT nilai.studentNIS, nilai.studenttestmark, (SELECT COUNT(*) FROM tblStudentGrades WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC; I've been looking around mySQL documentation and read that subquery can be redefined as INNER JOIN or using two SQL statement via variable? I have no idea on the basics of how to set it out though. Could one of you please help give a me a sample on how this kind of query should be done on mySQL? Is it possible to do it in single line? And without having to use PHP/Perl scripts? Or maybe I should have approach it differently?
View Replies !
Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking. For instance: Username | Rank steve | 6000 bob | 5000 jane | 4000 chris | 3000 brian | 2000 tim | 1000 molly | 0 Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following: bob | 5000 jane | 4000 chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this. brian | 2000 tim | 1000 What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from. For instance, lets say Chris is row 583 of my database... how do I retrieve that number?
View Replies !
Order Per "Group By" Groups
a table example: Code: ID | USER | VALUE | 1 | mike | 8 2 | mike | 10 3 |mike | 12 4 | john | 10 5 | john | 12 6 | john | 15 Desired result grouped by USER: ID | USER | VALUE | 6 | john | 15 3 | mike | 12 as you can see, I'm ordering the group result by the last value of ID column for each user, then order by user name. In other words, I need to retrieve the max ID value for each user. also order the result by user name. How can I do that ? Myabe there's no need of grouping,
View Replies !
Sub Counting For Each Row
For each row in my table, I'm getting stuck doing a count. An extract of the table is as follows: user_id | name | favourite_user 1 | "person 1" | 2 2 | "person 2" | 1 3 | "person 3" | 1 4 | "person 4" | 2 5 | "person 5" | 2 I'm trying to select all rows from the table, and at the same time, a count of how popular each person is. In two queries, this can be achieved by: SELECT * FROM TABLE; SELECT favourite_user, COUNT(*) FROM TABLE GROUP BY favourite_user How can I combine the queries so that for each person I return their data and a count of their popularity?
View Replies !
Counting Problem
I need your help here, I've been trying to solve this for hours and it's driving me crazy. It's yet another counting problem. Let's say I have a "friends" table looking like this: +-------------+----------------+--------+ | Town | Name | Eyes | +-------------+----------------+--------+ | Paris | Nicolas | blue | | Paris | Claire | blue | | Paris | Simon | brown | | Paris | Marie | black | | NY | Jason | blue | | NY | Frank | green | | NY | Amy | blue | +-------------+----------------+--------+ What I want is to know the number of friends with a particular eye color living in each town, like this: +------------+------+-------+-------+-------+ | Town | blue | brown | green | black | +------------+------+-------+-------+-------+ | NY | 2 | 0 | 1 | 0 | | Paris | 2 | 1 | 0 | 1 | +------------+------+-------+-------+-------+ I've always been very bad in mastering JOINs, and here I'm at a complete loss. Is there a charitable soul out there that can show me how to solve this?
View Replies !
Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to put something to the effect that, say, X days have elapsed since a given date. For example, suppose in my table, I have a boolean field and a date field. I want to create a query asking for those rows where X days have elapsed from the date field in a row and the boolean, say, is false. Do I need to create a third field, called, say "X" for how many days have elapsed between the date field and today, and update every row in the table every day?
View Replies !
Virtuel-Counting-Row
I use the database mysql v.4. My problem is... I have a select like: select * from user where language = "de"; the result are then: id name =================== 1 max 5 tim 99 otto so and what i need is a virtuell row (pos) with a counting index of the result: id name pos =================== === 1 max 1 5 tim 2 99 otto 3 how can i make this with mysql? My first idea was to made that with views but mysql can't do that!
View Replies !
Counting 2 Tables
I have 2 tables, with the same column names that i need to count. Where i try to do this run query: SELECT name, COUNT(name) FROM users, place GROUP BY name I get this error: #1052 - Column 'name' in field list is ambiguous
View Replies !
Counting By Age/group
how would i go about doing something like getting ages from database and then grouping them into age group? eg 18-20, 20-24 etc.. I could do that using PHP (i think, i have logic thought out) but was wondering if it would be easier to do in mysql
View Replies !
Counting Clicks
I want to create a click counter. When ever a user clicks a link the counter updates. I am using PHP for server side scripting and mySQL as backend database. If somebody could tell me about any mySQL database level procedure/trigger or PHP script or any CGI or pearl script for counting clicks,
View Replies !
Counting Distinct
I have a field that contains filenames. I'd like to count how many times a certain file name appears in that field. So, for example if I have 3 unique filenames, I would like to be able to see something like this: file1 = 120 file2 = 109 file3 = 76 The first part seems should be: select count(file) from download But how to specify returning a count for each distinct file?
View Replies !
Counting Fields
I need to retrieve the number of occurences of each distinct field in a specific column. This is what I am doing right now: Code: SELECT DISTINCT referrer FROM counter and for each record that that query returns: Code: SELECT COUNT(*) AS count FROM counter WHERE referrer = '".mysql_real_escape_string( $row[ 'referrer' ] )."' (using PHP) is there a way to make a single SQL query that will count the occurences of each distinct field of a specified column?
View Replies !
Counting Occurrences
I have a table Users which contains the id and vote1, vote2....vote10. The data stored in each vote field is the id of a product they are voting for as their favourite 10 products out of lots of different products. are there any functions in sql that allow me to count the occurrences of product ids in all vote1 and then vote2 and so on? With thousands of products I dont want to have to count for each one, only the ones that have been voted for.
View Replies !
Counting Results
i'm trying to find a way of counting the results in a mysql table. Say I have the fields CAR_MAKE | CAR_MODEL | COLOUR | Is there are way of saying car_make=bmw car_model=z3 colour=black how many of these are there im my table
View Replies !
Counting The Number
I'm having difficulty figuring out how count the number of each distinct value in a column.Say I have a column called 'score' that contains a score value of 1-4, how would I could how many 1s, 2s, 3s and 4s there were in this column?
View Replies !
Counting The Records
is it possible to do this in one statement: I wish to order by a certain field and count the records before a specific record, so as to get a sort of ranking.
View Replies !
Counting Total Row
I am trying to select the total row count from two different tables. I am getting a result back but it is returning the same count for both even though they have different amounts. This is what I have so far:- SELECT COUNT(companies.comp_id) as tcustomers, COUNT(project_id) as tprojects FROM companies, projects as you can see I have two tables, companies and projects.
View Replies !
Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement? so far I have: select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date) but this doesn't include the 'zero' days (days when there were zero messages).
View Replies !
Hour Counting
I keep in a ltable 'log' the time and date each user loged-in, and the time and tahe each user loged-out... among other things. What I want to do is a report to show how long each user was loged in the system. I'm using MySQL 4.0.15. I was thinking if I could do it with a single query but I cant figure out how. Table log: ....
View Replies !
Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.
View Replies !
Counting Set Items
Is it possible to count a set of items? Example count("2,3,4,5",","); Would return 4 Count items based on the delimeter? Or just count set items.
View Replies !
Counting With LIMIT
I'm trying to retrieve the number of records found using an offset (with LIMIT), however I get no results back eg.: SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10 Produces: MySQL returned an empty result set (i.e. zero rows) However, removing the LIMIT eg: SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s' Produces: count(propID) 38 I don't, though, want a count of all the records. The LIMIT works without the count eg.: SELECT propID FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10 Produces Showing rows 0 - 9 (eg. 10 records) But I only want the count, not the rows - arggghh! How can I get back a count of a subset of the data? Is there another way of syntaxing this query?
View Replies !
Counting Tables
i've been through this and a few forums and the docs, and don't seem to have come across a way of counting how many tables are in a database. specifically while using a like 'sometable%' would be useful for me is this possible? if there is not a command for this, i see that mysql>show tables like 'sometable%'; returns a table count at the bottom, maybe i can retrieve that somehow?
View Replies !
Counting Elements
I got sql query like this that worked fine on a server, then a moved my site to another server and it doesn't work. UPDATE user SET on_time = (SELECT on_time FROM review WHERE userID = '$userID' GROUP BY on_time ORDER BY COUNT (*) DESC LIMIT 1) WHERE userID = '$userID'"); I found that ORDER BY COUNT (*) is the problem but don't know how to get the most popular on_time value to update the user table. The query below doesn't work because I need to get only the on_time value, not on_time + count. UPDATE user SET on_time = (SELECT on_time, COUNT( * ) AS count FROM review WHERE userID = '$userID' GROUP BY on_time ORDER BY count DESC LIMIT 1) WHERE userID = '$userID'");
View Replies !
|
TRACKER
