Long story short, what fields should I put after the GROUP BY clause ? SELECT questions.question_id, questions.title, questions.content, questions.view_count, questions.posted_on, users.user_id, users.group_id, users.username, users.first_name, users.last_name COUNT(answers.answer_id) AS answer_count FROM (questions) JOIN answers ON q...
| one | two | ------------- | A | 1 | | A | 2 | | B | 1 | | B | 3 | | C | 1 | | C | 4 | I would like to get no repeats in any column in a query, so the standard SELECT DISTINCT one,two FROM table; or SELECT * FROM table GROUP BY one,two; doesn't quite work, because it looks for distinct in all rows, which in th...
I'm trying to do row versioning using an indexed view, grouping records by their key and timestamp, and taking the max(timestamp) record. This is fine, but the query I have used (see the view below) does a self join, meaning it can't be used in an indexed view, which i think will be essential to performance. Is there a way to rewrite the...
When you use Group By how do you keep the other fields in sync when using aggregates Here I am trying to find the min value of col4 when col2 = xxx select col1, col2, col3, min(col4) from table where col2 = 'xxx' group by col3 I can get the minimum value in col4 but col1 is not correct but col2, col3, col4 are. Can anyone show me ho...
The Query: SELECT MemberId, a.MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName Table a MemberID MemberName -------------- ---------- 1 Al 1 Al 3 A2 Table b MemberName...
Hello, I'm currently using PHP/MySQL to select some data from my database. My current SELECT statement is: mysql_query("SELECT * FROM tblpm WHERE receiver_id ='$usrID' GROUP BY thread_id ORDER BY unique_id DESC") or die(mysql_error()); There are some columns that have the same "thread-id", thus, I am using the GROUP BY, so that only...
After much Google searching and code experimentation, I'm stumped on a complex C# LINQ-to-objects problem which in SQL would be easy to solve with a pair of ROW_NUMBER()...PARTITION BY functions and a subquery or two. Here's, in words, what I'm trying to do in code-- the underlying requirement is removing duplicate documents from a lis...
I want to log certain activities in MySql with a timecode using time(). Now I'm accumulating thousands of records, I want to output the data by sets of hours/days/months etc. What would be the suggested method for grouping time codes in MySQL? Example data: 1248651289 1248651299 1248651386 1248651588 1248651647 1248651700 1248651707 ...
Imagine the following schema and sample data (SQL Server 2008): OriginatingObject ---------------------------------------------- ID 1 2 3 ValueSet ---------------------------------------------- ID OriginatingObjectID DateStamp 1 1 2009-05-21 10:41:43 2 1 2009-05-22 12:11:51 3 1 ...
I have a column which looks something like this: CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column. Is there a way to GROUP BY a column alias such as some_product in this case,...
Ok here's my dilemma. I am looking for a way to consolidate the data in a group using SimpleXML in PHP. Here's what I mean. Let's say I have an xml that looks like this: <favorites> <interesting> <type>Movie</type> <character>James Bond</character> <name>Casino Royale</name> </interesting> <interes...
I have a situation where I have one table of titles (t1) and another table with multiple links that reference these titles (t2) in a one to many relationship. What I want is the full list of titles returned with a flag that indicates if there is a specific link associated with it. Left Join and Group By: SELECT t1.id , t1.titl...
I writing a query within ms sql server 2005 and having some trouble getting it to run. I need my query to include: Include all docs from Date Range 2009-07-20 to 2009-08-04 Include a Sum column using the 'Size' field Include a record count column Group By Name select Name, count(*) AS FileCount, SUM(Size) From alldocs Group by DirNa...
Hi, I am using Linq to dataset to query a datatable. If i want to perform a group by on "Column1" on data table, I use following query var groupQuery = from table in MyTable.AsEnumerable() group table by table["Column1"] into groupedTable select new { x = groupedTable.Key, y = groupedTable.Count() } Now I want to perform group...
So, I understand that group by works basically like this: var query = from c in context.Contacts join o in context.Orders on c.Id on o.CustomerId group c by o.Id select new { g.Key, g.Sum(c => c.Whatever) }; Grouping like that only allows me access to the contents of c. But what if I wanted data fro...
I have a table Auditing logins, I'd like to just extract the latest login for each user, which the below sql accomplishes. How would I format the sql query into a linq to sql query? SELECT * FROM AuditTable adt1 inner join UserTable usr on adt1.[UserName] = usr.[User_Id] WHERE [TimeStamp] = ( SELECT MAX([TimeStamp]) ...
I find that when trying to construct complex MySQL joins and groups between many tables I usually run into strife and have to spend a lot of 'trial and error' time to get the result I want. I was wondering how other people approach the problems. Do you isolate the smaller blocks of data at the end of the branches and get these working f...
Hi wondering if perhaps someone could shed some light on the below error. The sql works fine locally but i get the the below error remotely. SQL query: SELECT COUNT(node.nid), node.nid AS nid, node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value FROM ...
I have a MEMBERS table with the following relevant columns: Name JoinDate Level --1=Gold,2=Silver,3=Bronze** I want to create a single query to return a membership summary that lists the total number who joined by year and by membership level. Basically, the columns in my resultset would be something like this: | YEAR | ...
Hi, I have 4 tables for each month; oct, dec, feb, apr. They have the same schema. I need to extract the data from all the tables using the same query and grouping it for one attribute. I know how to do it for one table but I would like to combine the results into one query and then dump the output into the file. Here is my sample qu...