Group by and order per field

Databases
1

Hi

i have a simple table master and the master_lang that holds the language data

I am trying to write an sql command that will return the data based on current language.

Here is an example :

record 1 has in master_lang languages EN,FR
record 2 has in master_lang languages EN
record 3 has in master_lang languages FR
record 4 has in master_lang languages EN,FR

So if current language is EN i want to return
record 1
record 2
record 4
(order by the name on EN language)

record 3
(order by the name on FR language)

If i can make it more simple i would say
" give me first all records from current language order by name and then for all other languages order by name "

Here is my SQL so far

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

BUT GROUP BY master.masterID does not follow ORDER BY

I think that first Group and then ORDER

I dont want to

INNER JOIN lang_table as langData ON 
langData.masterID=master.masterID
AND langData.lang='currentLnag'

because it will return only the currentLang

Any suggest is welcome

Thank

2

you shouldn’t store multiple values in a single column. you should normalize your data so that the languages are broken out into a separate table, one new row per language spoken/written.

3

Here is the db scheme

master table

id
1
2
3

lang table
1 | EN | en data for master_id 1
1 | FR | fr data for master_id 1
2 | EN | en data for master_id 2
3 | FR | fr data for master_id 3

Am i missing something regarding your answer

4

you shouldn’t have a GROUP BY clause

SELECT please
     , list
     , the
     , columns
     , you
     , want
  FROM master_table AS master 
INNER 
  JOIN lang_table AS langData 
    ON langData.masterID = master.masterID 
ORDER 
    BY CASE WHEN langData.lang = 'currentLang' 
            THEN 1 ELSE 999 END
     , master.name DESC LIMIT 0,10
5

Thank for your reply.

The problem leaving GROUP BY from SQL is that when LIMIT 0,10 i dont get 10 records form master table i get 10 records from lang table.

That means that if record 1 has EN,FR i get 2 records etc.

Thats why grouping.

BUT i was expected that grouping will return currentLang if exist or any other if not currentLang exist

based on

ORDER 
    BY CASE WHEN langData.lang = 'currentLang' 
            THEN 1 ELSE 999 END

Meaning in the case of record 1 having EN,FR the group will return the EN value of lang table.

That what i am trying to achieve

In my SQL now if EN has value test and FR has value 111, it returns the FR value

based on

master.name DESC

BUT the SQL has

  • FIRST order by lang
  • THEN order by name

Code

ORDER 
    BY CASE WHEN langData.lang = 'currentLang' 
            THEN 1 ELSE 999 END
     , master.name DESC LIMIT 0,10

and seems that grouping “ingore”

CASE WHEN langData.lang = 'currentLang' THEN 1 ELSE 999

or something is missing

Thanks

6

then you has a real problem, don’ts you :smiley: :smiley:

you cannot combine the dreaded, evil “select star” with GROUP BY because the behaviour is not what you think

GROUP BY collapses multiple rows produced by the FROM clause into a single output row in the result set

you’re going to have to re-think what you want with regard to the LIMIT