In C# it would be like this:
table
.GroupBy(row => row.SomeColumn)
.Select(group => group
.OrderBy(row => row.AnotherColumn)
.First()
)
Linq-To-Sql translates it to the following T-SQL code:
SELECT [t3].[AnotherColumn], [t3].[SomeColumn]
FROM (
SELECT [t0].[SomeColumn]
FROM [Table] AS [t0]
GROUP BY [t0].[SomeColumn]
) AS [t1]
OUTER APPLY (
SELECT TOP (1) [t2].[AnotherColumn], [t2].[SomeColumn]
FROM [Table] AS [t2]
WHERE (([t1].[SomeColumn] IS NULL) AND ([t2].[SomeColumn] IS NULL))
OR (([t1].[SomeColumn] IS NOT NULL) AND ([t2].[SomeColumn] IS NOT NULL)
AND ([t1].[SomeColumn] = [t2].[SomeColumn]))
ORDER BY [t2].[AnotherColumn]
) AS [t3]
ORDER BY [t3].[AnotherColumn]
But it is incompatible with MySQL.
Yet another way to do it (without the primary key) would be using the JSON functions:
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
from sometable group by somecolumn
or pre 5.7.22
select somecolumn,
json_unquote(
json_extract(
concat('["', group_concat(othercolumn separator '","') ,'"]')
,"$[0]" )
)
from sometable group by somecolumn
Ordering (or filtering) can be done before grouping:
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
from (select * from sometable order by othercolumn) as t group by somecolumn
... or after grouping (of course):
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) as other
from sometable group by somecolumn order by other
Admittedly, it's rather convoluted and performance is probably not great (didn't test it on large data, works well on my limited data sets).
Yet another way to do it
Select max from group that works in views
SELECT * FROM action a
WHERE NOT EXISTS (
SELECT 1 FROM action a2
WHERE a2.user_id = a.user_id
AND a2.action_date > a.action_date
AND a2.action_type = a.action_type
)
AND a.action_type = "CF"
SELECT
t1.*
FROM
table_name AS t1
LEFT JOIN table_name AS t2 ON (
t2.group_by_column = t1.group_by_column
-- group_by_column is the column you would use in the GROUP BY statement
AND
t2.order_by_column < t1.order_by_column
-- order_by_column is column you would use in the ORDER BY statement
-- usually is the autoincremented key column
)
WHERE
t2.group_by_column IS NULL;
With MySQL v8+ you could use window functions
Here's another way you could try, that doesn't need that ID field.
select some_column, min(another_column)
from i_have_a_table
group by some_column
Still I agree with lfagundes that you should add some primary key ..
Also beware that by doing this, you cannot (easily) get at the other values is the same row as the resulting some_colum, another_column pair! You'd need lfagundes apprach and a PK to do that!
I suggest to use this official way from MySql:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article
GROUP BY s2.article)
ORDER BY article;
With this way, we can get the highest price on each article
Why not use MySQL LIMIT keyword?
SELECT [t2].[AnotherColumn], [t2].[SomeColumn]
FROM [Table] AS [t2]
WHERE (([t1].[SomeColumn] IS NULL) AND ([t2].[SomeColumn] IS NULL))
OR (([t1].[SomeColumn] IS NOT NULL) AND ([t2].[SomeColumn] IS NOT NULL)
AND ([t1].[SomeColumn] = [t2].[SomeColumn]))
ORDER BY [t2].[AnotherColumn]
LIMIT 1
I based my answer on the title of your post only, as I don't know C# and didn't understand the given query. But in MySQL I suggest you try subselects. First get a set of primary keys of interesting columns then select data from those rows:
SELECT somecolumn, anothercolumn
FROM sometable
WHERE id IN (
SELECT min(id)
FROM sometable
GROUP BY somecolumn
);
MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
This means that @Jader Dias's solution wouldn't work everywhere.
Here is a solution that would work when ONLY_FULL_GROUP_BY
is enabled:
SET @row := NULL;
SELECT
SomeColumn,
AnotherColumn
FROM (
SELECT
CASE @id <=> SomeColumn AND @row IS NOT NULL
WHEN TRUE THEN @row := @row+1
ELSE @row := 0
END AS rownum,
@id := SomeColumn AS SomeColumn,
AnotherColumn
FROM
SomeTable
ORDER BY
SomeColumn, -AnotherColumn DESC
) _values
WHERE rownum = 0
ORDER BY SomeColumn;
How about this:
SELECT SUBSTRING_INDEX(
MIN(CONCAT(OrderColumn, '|', IFNULL(TargetColumn, ''))
), '|', -1) as TargetColumn
FROM table
GROUP BY GroupColumn
Best performance and easy to use:
SELECT id, code,
SUBSTRING_INDEX( GROUP_CONCAT(price ORDER BY id DESC), ',', 1) first_found_price
FROM stocks
GROUP BY code
ORDER BY id DESC
You should use some aggregate function to get the value of AnotherColumn that you want. That is, if you want the lowest value of AnotherColumn for each value of SomeColumn (either numerically or lexicographically), you can use:
SELECT SomeColumn, MIN(AnotherColumn)
FROM YourTable
GROUP BY SomeColumn
Some hopefully helpful links:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html
Select the first row for each group (as ordered by a column) in Mysql .
We have:
a table: mytable
a column we are ordering by: the_column_to_order_by
a column that we wish to group by: the_group_by_column
Here's my solution. The inner query gets you a unique set of rows, selected as a dual key. The outer query joins the same table by joining on both of those keys (with AND).
SELECT * FROM
(
SELECT the_group_by_column, MAX(the_column_to_order_by) the_column_to_order_by
FROM mytable
GROUP BY the_group_by_column
ORDER BY MAX(the_column_to_order_by) DESC
) as mytable1
JOIN mytable mytable2 ON mytable2.the_group_by_column =
mytablealiamytable2.the_group_by_column
AND mytable2.the_column_to_order_by = mytable1.the_column_to_order_by;
FYI: I haven't thought about efficiency at all for this and can't speak to that one way or the other.
I have not seen the following solution among the answers, so I thought I'd put it out there.
The problem is to select rows which are the first rows when ordered by AnotherColumn
in all groups grouped by SomeColumn
.
The following solution will do this in MySQL. id
has to be a unique column which must not hold values containing -
(which I use as a separator).
select t1.*
from mytable t1
inner join (
select SUBSTRING_INDEX(
GROUP_CONCAT(t3.id ORDER BY t3.AnotherColumn DESC SEPARATOR '-'),
'-',
1
) as id
from mytable t3
group by t3.SomeColumn
) t2 on t2.id = t1.id
-- Where
SUBSTRING_INDEX(GROUP_CONCAT(id order by AnotherColumn desc separator '-'), '-', 1)
-- can be seen as:
FIRST(id order by AnotherColumn desc)
-- For completeness sake:
SUBSTRING_INDEX(GROUP_CONCAT(id order by AnotherColumn desc separator '-'), '-', -1)
-- would then be seen as:
LAST(id order by AnotherColumn desc)
There is a feature request for FIRST()
and LAST()
in the MySQL bug tracker, but it was closed many years back.
Source: Stackoverflow.com