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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 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 !
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 !
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 !
|
TRACKER
