#group-by

Fixed Retrieving the last record in each group #dev #it #asnwer

Retrieving the last record in each group

There is a table messages that contains data as shown below:

Id Name Other_Columns - 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1

If I run a query select * from messages group by name, I will get the result as:

1 A A_data_1 4 B B_data_1 6 C C_data_1

What query will return the following result?

3 A A_data_3 5 B B_data_2 6…

Fixed Select first row in each GROUP BY group? #dev #it #asnwer

Select first row in each GROUP BY group?

As the title suggests, I’d like to select the first row of each set of rows grouped with a GROUP BY.

Specifically, if I’ve got a purchases table that looks like this:

SELECT * FROM purchases;

id | customer | total -++ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1

I’d like to query for the id of the largest purchase (total) made by each customer.…

Fixed MySQL Query GROUP BY day / month / year #dev #it #asnwer

MySQL Query GROUP BY day / month / year

Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like:

SELECT COUNT(id) FROM stats WHERE record_date.YEAR = 2009 GROUP BY record_date.YEAR

Or even:

SELECT COUNT(id) FROM stats GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.

T…

Fixed Group by in LINQ #dev #it #asnwer

Group by in LINQ

Let’s suppose if we have a class like

class Person { internal int PersonID; internal string car ; }

Now I have a list of this class: List<Person> persons;

Now this list can have instances multiple same PersonIDs, for ex.

persons[0] = new Person { PersonID = 1, car = "Ferrari" }; persons[1] = new Person { PersonID = 1, car = "BMW" }; persons[2] = new Person { PersonID = 2, car…

How to: How to concatenate strings of a string field in a PostgreSQL 'group by' query?

How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:

ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave

and I wanted to group by company_id to get something like:

COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave

There is a built-in function in…

How to: Get top 1 row of each group

Get top 1 row of each group

I have a table which I want to get the latest entry for each group. Here’s the table:

DocumentStatusLogs Table

|ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 | S1 | 8/02/2011 | | 1| 2 | S1 | 7/28/2011 | | 4| 2 | S2 | 7/30/2011 | | 5| 2 | S3 | 8/01/2011 | | 6| 3 | S1 | 8/02/2011 |

The table will be grouped by…

Fixed Group By Multiple Columns #dev #it #asnwer

Group By Multiple Columns

How can I do GroupBy Multiple Columns in LINQ

Something similar to this in SQL:

SELECT * FROM <TableName> GROUP BY <Column1>,<Column2>

How can I convert this to LINQ:

QuantityBreakdown ( MaterialID int, ProductID int, Quantity float ) INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity) SELECT MaterialID, ProductID, SUM(Quantity) FROM @Transactions GROUP BY…