SQL SERVER odd line queries

Asked

Viewed 197 times

2

I need to create query script that returns from the table below only its odd lines, ordered upwards:

DECLARE @table TABLE (coluna1 varchar(50))
INSERT INTO @table
VALUES ('Anthony'),('Miguel'),('Benjamin'),('Lucca'),('Enzo'),('Martim'), ('Noah'),('Gael'),('Henrique'),('Heitor'),('Nícolas'),('Bernardo'), ('Filipe'),('Arthur'),('Apolo'),('José'),('João'),('Antônio'), ('Vicente'),('Alice'),('Luna'),('Valentina'),('Isabela'),('Larissa' ), ('Laura'),('Antonella'),('Victoria'),('Julia'),('Manuela'),('Ana'), ('Camila'),('Beatriz'),('Elisa'),('Sophia'),('Mayara'),('Maria')

Note: I cannot change the above script by creating another field as id.

1 answer

4

Use ROW_NUMBER to list the lines, divide the line by 2 where the rest of that division is 1. The operator %(MOD) will be necessary in this case.

Notice I used a subConsulta uncorrelated, i.e., a subConsulta where the external result depends on the internal result.

An example much like yours;

CREATE TABLE LINHASIMPARES (TEXTO VARCHAR(10) );


INSERT INTO LINHASIMPARES (TEXTO)
VALUES('MARCONI'),('MAGNO'), ('ALEX');


SELECT L.TEXTO,
       L.LINHA
FROM
  (SELECT TEXTO,
          ROW_NUMBER() OVER(
                            ORDER BY TEXTO ASC) AS LINHA
   FROM LINHASIMPARES) L
WHERE L.LINHA % 2 = 1;

Sqlfiddle

Browser other questions tagged

You are not signed in. Login or sign up in order to post.