I'm trying to get results close to a specific record. I tried to use ABS, but I could not do it using it. The result limit is 5 .
Data
MARCA NOME_MARCA MEDIA
8 TOYOTA 100
11 HONDA 100
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
4 VOLKSWAGEN -100
I have these data above. I would like to make a query that would return the next two and the previous two. For example, the user is FORD. I want you to return the following in a query:
MARCA NOME_MARCA MEDIA
8 TOYOTA 100
11 HONDA 100
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
FORD is in the middle by coincidence. But if it's FIAT, I want it to show like this:
MARCA NOME_MARCA MEDIA
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
4 VOLKSWAGEN -100
That is, I always want the tag I bring as a parameter, the previous two (if it has) and the next two (if it has).
My SELECT is a bit large, because it's a calculation I make. But I'll post below:
SELECT
'empresas'.'id_marca' AS 'id_marca',
'brands'.'marca' AS 'marca',
sum('pro'.'qtd_pro') AS 'qtd_pro',
sum('det'.'qtd_det') AS 'qtd_det',
sum('neu'.'qtd_neu') AS 'qtd_neu',
round(
(
(
(
sum(
COALESCE ('pro'.'qtd_pro', 0)
) - sum(
COALESCE ('det'.'qtd_det', 0)
)
) / (
(
sum(
COALESCE ('pro'.'qtd_pro', 0)
) + sum(
COALESCE ('neu'.'qtd_neu', 0)
)
) + sum(
COALESCE ('det'.'qtd_det', 0)
)
)
) * 100
),
0
) AS 'nps'
FROM
(
(
(
(
'empresas'
LEFT JOIN (
SELECT
'empresa_ratings'.'id_empresa' AS 'id_empresa',
COALESCE (
count(
'empresa_ratings'.'rating_nps'
),
0
) AS 'qtd_pro'
FROM
'empresa_ratings'
WHERE
(
'empresa_ratings'.'rating_nps' >= 9
)
GROUP BY
'empresa_ratings'.'id_empresa'
) 'pro' ON (
(
'pro'.'id_empresa' = 'empresas'.'id'
)
)
)
LEFT JOIN (
SELECT
'empresa_ratings'.'id_empresa' AS 'id_empresa',
COALESCE (
count(
'empresa_ratings'.'rating_nps'
),
0
) AS 'qtd_det'
FROM
'empresa_ratings'
WHERE
(
'empresa_ratings'.'rating_nps' <= 6
)
GROUP BY
'empresa_ratings'.'id_empresa'
) 'det' ON (
(
'det'.'id_empresa' = 'empresas'.'id'
)
)
)
LEFT JOIN (
SELECT
'empresa_ratings'.'id_empresa' AS 'id_empresa',
COALESCE (
count(
'empresa_ratings'.'rating_nps'
),
0
) AS 'qtd_neu'
FROM
'empresa_ratings'
WHERE
(
'empresa_ratings'.'rating_nps' BETWEEN 7
AND 8
)
GROUP BY
'empresa_ratings'.'id_empresa'
) 'neu' ON (
(
'neu'.'id_empresa' = 'empresas'.'id'
)
)
)
JOIN 'brands' ON (
(
'brands'.'id' = 'empresas'.'id_marca'
)
)
)
WHERE
(
('pro'.'qtd_pro' IS NOT NULL)
OR ('det'.'qtd_det' IS NOT NULL)
OR ('neu'.'qtd_neu' IS NOT NULL)
)
GROUP BY
'empresas'.'id_marca'
ORDER BY
nps desc