Wyciągnięcie jednej, najwyższej wartości

Witam, walczę z zapytaniem w bazie danych i niestety muszę prosić o pomoc.

Generalnie jest to zapytanie, które wstawia odpowiednie ilości punktów do bazy danych na podstawie danych z innej tabeli. Poniżej wstawiam fragment odpowiedzialny za wyciągnięcie wartości.

SELECT
	wp.user_id,
	UNIX_TIMESTAMP(s.payment_date) as time,
	
	(SUM(s.net_value) DIV 100) * (p.points_per_chunk - main_p.points_per_chunk) as creds,

	IF(s.status = 1,
		CONCAT("Punkty ", s.manufacturer, ": ", s.invoice_id),
		CONCAT("Punkty 2 ", s.manufacturer, ": ", s.invoice_id)
	) as entry,
	IF(s.status = 1, "reward", "future_reward") as ref,
	s.invoice_id as ref_id
FROM
	pop_sales s
LEFT JOIN wp_usermeta wp ON wp.meta_key = "nip" AND wp.meta_value = s.nip

RIGHT JOIN pop__promotions p ON p.manufacturer LIKE CONCAT("%", s.manufacturer, "%")

LEFT JOIN pop_promotions main_p ON s.payment_date BETWEEN main_p.start_date 
	AND IF(main_p.end_date IS NULL, NOW(), main_p.end_date)
		
LEFT JOIN wp_users wpu ON wp.user_id = wpu.ID
WHERE wp.user_id IS NOT NULL
	AND s.payment_date BETWEEN p.start_date AND p.end_date
	AND wpu.user_registered < s.payment_date
	AND main_p.manufacturer IS NULL
GROUP BY s.manufacturer

Samo w sobie zapytanie działa, ale pojawia się jeden problem, który sprowadza się do tego fragmentu:

RIGHT JOIN pop_promotions p ON p.manufacturer LIKE CONCAT("%", s.manufacturer, "%")

Otóż wyciąga ono z tabeli wiersze, które zawierają daną nazwę firmy i odpowiadającą im ilość punktów. Problem w tym, że wierszy z daną firmą może być kilka. Co więcej, jeden wiersz może zawierać listę składającą się z kilku firm. Chciałbym jednak wyciągnąć ten wiersz, który ma największą ilość punktów. Niestety ani przez SELECT MAX(), ani przez sortowanie i wyciąganie jednej wartości lub grupowanie nie byłem w stanie tego zrobić. Grupowanie chyba nie zda tu egzaminu, bo nawet jak jedna firma występuje kilka razy, to może ona być w wierszy z innymi firmami. Poniżej jeszcze zrzut wyglądu tej tabeli:

A jak wg Ciebie powinien wyglądać poprawny wynik, dla powyższego obrazka ?

To akurat zależy od drugiej tabeli, bo tutaj porównywane są daty i nazwa firmy. W momencie, gdy w pierwszej tabeli pojawia się firma "3W IZOLACJE" to chciałbym, żeby wyciągnęło mi najwyższą wartość punktów, czyli 16.00, w tym przypadku akurat ostatni wiersz. W przypadku firmy ACO wyciąga 12.00 punktów i to akurat działa. Problem się pojawi, gdy firma ACO pojawi się w tabeli drugi raz. Wtedy niby coś wylicza, ale nie do końca wiem na jakiej podstawie, bo wynik jest błędny.

Twoje rozumowanie wydaje się słuszne.
Są dwa rozwiązania:

• utworzyć podzapytanie , które sortuje tabelę po points_per_chunk dla danego manifacturer i ograniczyć wynik do jednego, pierwszego wiersza. Piszesz, że to robiłeś, pytanie czy stosowałeś podzapytanie albo np. widok.
• jeszcze raz od nowa napisać to zapytanie - najlepsza metoda !!! Wiele rzeczy dziwnie się wyjaśnia.

Niestety bez rzeczywistych danych i możliwość "zabawy" niewiele pomogę :( Taka smutna rzeczywistość.

Problem w tym, że wierszy z daną firmą może być kilka. Co więcej, jeden wiersz może zawierać listę składającą się z kilku firm.

W Postgresie chyba używałem funkcji unnest i można by było tego użyć do rozbicia tych rekordów na pojedyncze firmy.

Robert Karpiński napisał(a):

Niestety bez rzeczywistych danych i możliwość "zabawy" niewiele pomogę :( Taka smutna rzeczywistość.

Jasne, rozumiem :)

Dzięki Panowie za porady. Spróbuję dalej z tym powalczyć. Jakby ktoś jeszcze miał jakieś sugestie to będę wdzięczny.

Napisz funkcję.
SELECT ... HAVING(max(score)) GROUP BY extract_joined(nm_companies);

Jeśli nm_companies będzie
jabłko
jabłko,gruszka

To funkcja extract_joined ma usunąć ",gruszka", w wyniku czego dostaniesz samo jabłko, a identycznym członem zajmie się na spokojnie f() grupująca.

Ogólnie ma wyciągnąć keyword jabłko z łańcucha i dzięki temu pogrupuje jako identyczne wartości.

Musisz się z joinować z podzapytaniem, które zwróci ci oczekiwane wartości, czyli jeden wiersz z firmą i max wartością point_per_chunk, w przypadku listy trzeba rozdzielić wiersz na tyle na ile jest wartości w liście.

SELECT
	manufacturer
	,max(point_per_chunk) point_per_chunk
FROM (  
	SELECT
		SUBSTRING_INDEX(SUBSTRING_INDEX(p.manufacturer, ',', numbers.n), ',', -1) manufacturer,
		point_per_chunk
	FROM
		numbers 
	    INNER JOIN p ON CHAR_LENGTH(p.manufacturer)-CHAR_LENGTH(REPLACE(p.manufacturer, ',', ''))>=numbers.n-1
	) as dt
group by
    manufacturer

http://sqlfiddle.com/#!9/f1cc03/5

wtedy w joinie masz operator = zamiast like

Ważne, żeby tabela numbers miała conajmniej tyle liczb ile ma elementów najdłuższa lista. Nie wiem jak to będzie wydajne, ale jak tak się pracuje na takich danych to trzeba rzeźbić...

Jeżeli używasz MySQL w wersji 8 to mozna też tak:

SELECT
	manufacturer
	,max(point_per_chunk) point_per_chunk
FROM (  
	select 
	   p.id
	   ,j.manufacturer  
	   ,p.point_per_chunk
	from 
	   p
	join json_table(
	   CONCAT('["', REPLACE(p.manufacturer, ',', '","') ,'"]'),
	  '$[*]' columns (manufacturer  varchar(50) path '$')
	   ) j
	) as dt
group by
    manufacturer

https://www.db-fiddle.com/f/4KfZJxR8sc6Hyy8XpQEKmE/74

@Panczo, dzięki, działa to pięknie! :)

Tylko niestety okazało się, że chyba mój zamysł nie do końca się sprawdził. Faktycznie pobiera najwyższą wartość punktową dla danego producenta, ale później sprawdza datę. Jeśli ta się nie pokrywa, to wtedy nie bierze już innej wartości tylko całkiem pomija danego producenta. Muszę to jeszcze przemyśleć i nieco przebudować. Niemniej jednak dziękuję bardzo za pomoc!