0

I'm a novice in the SQL world.

Right now, I have the following query:

SELECT b.co_curriculo, b.ds_nome_profissional AS nome, b.linkedin, 
        a.co_shorlist, cao_cliente.no_fantasia, 0 as recursado
FROM cao_curriculo_shortlist_cv a
JOIN cao_curriculo b ON b.co_curriculo = a.co_curriculo
JOIN cao_curriculo_shortlist ON cao_curriculo_shortlist.co_shortlist = a.co_shorlist
JOIN cao_os_atividade ON cao_os_atividade.co_atividade = cao_curriculo_shortlist.co_atividade
JOIN cao_usuario ON cao_usuario.co_usuario = cao_os_atividade.co_usuario
JOIN cao_sistema ON cao_os_atividade.co_sistema = cao_sistema.co_sistema
JOIN cao_cliente ON cao_cliente.co_cliente = cao_sistema.co_cliente
WHERE b.linkedin != ''

UNION


SELECT co_recusa_entrevista AS co_curriculo, ds_nome_profissional AS nome, 
        linkedin, cao_curriculo_recusa_entrevista.co_shortlist AS co_shorlist, 
        cao_cliente.no_fantasia, 1 AS recursado
FROM cao_curriculo_recusa_entrevista
JOIN cao_curriculo_shortlist ON cao_curriculo_shortlist.co_shortlist = cao_curriculo_recusa_entrevista.co_shortlist
JOIN cao_os_atividade ON cao_os_atividade.co_atividade = cao_curriculo_shortlist.co_atividade
JOIN cao_usuario ON cao_usuario.co_usuario = cao_os_atividade.co_usuario
JOIN cao_sistema ON cao_os_atividade.co_sistema = cao_sistema.co_sistema
JOIN cao_cliente ON cao_cliente.co_cliente = cao_sistema.co_cliente

WHERE linkedin != ''
ORDER BY 2 desc
    OFFSET 0
    FETCH FIRST 100 ROW ONLY

Testing this on MySQL Workbench gives me an error telling me

OFFSET (offset) is not a valid input at this position

By deleting OFFSET 0 FETCH FIRST 100 ROW ONLY, the code works perfectly returning me 2 rows.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
MetalKaiser
  • 1
  • 7
  • 1
    Isn't `OFFSET` not valid MySQL syntax? If so, what made you think it was? Are you converting a SQL Server query, for example? – underscore_d Jan 09 '20 at 14:43
  • Missing a LIMIT bit before Offset – RiggsFolly Jan 09 '20 at 14:44
  • 1
    `LIMIT 0, 100` instead of `OFFSET 0 FETCH FIRST 100 ROW ONLY` that is DB2 syntax I guess – Alex Jan 09 '20 at 14:48
  • OFFSET/FETCH FIRST is ANSI SQL. Perhaps not supported by MySQL? (Supported by newer versions of Oracle, PostgreSQL, MS SQL Server, Mimer SQL and DB2 etc.) – jarlh Jan 09 '20 at 14:48
  • @underscore_d `Isn't OFFSET not valid MySQL syntax? If so...` You've not got me a little unconfused with your phraseology there. What are you trying to say? – Strawberry Jan 09 '20 at 14:54
  • @Strawberry Is it not, in fact, the case, that `OFFSET` is not valid MySQL syntax? – underscore_d Jan 09 '20 at 15:00
  • https://www.slideshare.net/slideshow/view?login=Eweaver&preview=no&slideid=1&title=efficient-pagination-using-mysql – jarlh Jan 09 '20 at 15:01
  • @underscore_d Though I'm really a novice with SQL, I understand OFFSET and FETCH clauses are the options of the ORDER BY clause. – MetalKaiser Jan 09 '20 at 15:06
  • There is no one "SQL". There is ANSI SQL, and there are vendors that implement certain percentages of that and add their own custom keywords. Is it valid MySQL? – underscore_d Jan 09 '20 at 15:12
  • @underscore_d OFFSET is not invalid in MySQL. I don't know what FETCH is. – Strawberry Jan 09 '20 at 15:24
  • In mysql the limitation clause looks like `[LIMIT {[offset,] row_count | row_count OFFSET offset}]`. [SELECT Syntax](https://dev.mysql.com/doc/refman/8.0/en/select.html). Do not try to use Oracle Database syntax on MySQL... – Akina Jan 09 '20 at 16:25

1 Answers1

0

Sorry for the long wait.

The answers telling me about the LIMIT were right

SELECT b.co_curriculo, b.ds_nome_profissional AS nome, b.linkedin, a.co_shorlist, cao_cliente.no_fantasia, 0 as recursado
                                    FROM cao_curriculo_shortlist_cv a
                                    JOIN cao_curriculo b ON b.co_curriculo = a.co_curriculo
                                    JOIN cao_curriculo_shortlist ON cao_curriculo_shortlist.co_shortlist = a.co_shorlist
                                    JOIN cao_os_atividade ON cao_os_atividade.co_atividade = cao_curriculo_shortlist.co_atividade
                                    JOIN cao_usuario ON cao_usuario.co_usuario = cao_os_atividade.co_usuario
                                    JOIN cao_sistema ON cao_os_atividade.co_sistema = cao_sistema.co_sistema
                                    JOIN cao_cliente ON cao_cliente.co_cliente = cao_sistema.co_cliente
                                    WHERE b.linkedin != ''
                                    AND a.co_shortlist_cv_status != '6'

                                    UNION


                                    SELECT co_recusa_entrevista AS co_curriculo, ds_nome_profissional AS nome, linkedin, cao_curriculo_recusa_entrevista.co_shortlist AS co_shorlist, cao_cliente.no_fantasia, 1 AS recursado
                                    FROM cao_curriculo_recusa_entrevista
                                    JOIN cao_curriculo_shortlist ON cao_curriculo_shortlist.co_shortlist = cao_curriculo_recusa_entrevista.co_shortlist
                                    JOIN cao_os_atividade ON cao_os_atividade.co_atividade = cao_curriculo_shortlist.co_atividade
                                    JOIN cao_usuario ON cao_usuario.co_usuario = cao_os_atividade.co_usuario
                                    JOIN cao_sistema ON cao_os_atividade.co_sistema = cao_sistema.co_sistema
                                    JOIN cao_cliente ON cao_cliente.co_cliente = cao_sistema.co_cliente

                                    WHERE linkedin != ''
                                    ORDER BY 2 asc
                                    LIMIT $offset, $records_per_page
MetalKaiser
  • 1
  • 7