Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

GROUP BY return the first record [duplicate]

Tags:

mysql

group-by

As far as I know mysql GROUP BY groups to the last record found.

Is there any solution to GROUP BY the first record?

I have setup the ORDER in SQL command and I need GROUP BY return the first record and not the last.

EDIT

Here is the Query

SELECT 
  DISTINCT(master.masterID),
  langData.*,
  master.* 
FROM master_table as master 
INNER JOIN lang_table as langData ON 
langData.masterID=master.masterID 
GROUP BY master.masterID 
ORDER BY 
CASE 
    WHEN langData.lang='currentLang' THEN 1 ELSE 999 END , 
    master.name desc LIMIT 0,10 

The query above select the masterID for multi language table and suppose to return FIRST the records in currentLang and order them by name AND THEN all other languages.

Don't ask me why I don't set the language in JOIN. This is the way to be done.

So everything works fine so far expect the scenario that I have a record with languages en and fr. If currentLang is en then based on

langData.lang='currentLang' THEN 1 ELSE 999 END

the en order is 1 and fr order is 999 and instead of getting the value of en I get the value of fr.

That's why I want to group to the first row.

like image 561
ntan Avatar asked Mar 02 '10 11:03

ntan


People also ask

Does GROUP BY return duplicates?

GROUP BY only treats two rows as duplicates if all the column values in both the rows are the same. If even a single column value in either of the row is non-matching, they are treated as unique.

Does GROUP BY clause remove duplicates?

SQL Delete Duplicate Rows using Group By and Having Clause According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.

Does SELECT return duplicate rows?

If you do not include DISTINCT in a SELECT clause, you might find duplicate rows in your result, because SQL returns the JOB column's value for each row that satisfies the search condition. Null values are treated as duplicate rows for DISTINCT.


2 Answers

I assume you are talking of something like

SELECT  *
FROM    mytable
GROUP BY
        column

You shouldn't use unaggregated expressions in GROUP BY unless they are all same within the group.

If you want to return the record holding the least value of an expression within a group, use this:

SELECT  mo.*
FROM    (
        SELECT  DISTINCT column
        FROM    mytable
        ) md
JOIN    mytable mo
ON      mo.id = 
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.column = md.column
        ORDER BY
                mi.column, mi.someorder
        LIMIT 1
        )
like image 55
Quassnoi Avatar answered Oct 13 '22 03:10

Quassnoi


Add LIMIT 1 to your query.

like image 31
Daniel A. White Avatar answered Oct 13 '22 03:10

Daniel A. White