SQL SERVER. Boolean field of an alphabetically ordered list

Question

SQL SERVER. Boolean field of an alphabetically ordered list

Navigation

#1 by (-1 votes)

1

I have a table of people with various fields such as country, city, name, years ... What I already have is a query that, from each country and each city, brings out all the people who live there, arranged alphabetically.

So now what I want is a new field (esfirst) that says if that person is the first alphabetically speaking. As an example:

País      Ciudad     Nombre     Edad     esPrimero

Alemania  Múnich     Gerd       64       Sí
Alemania  Múnich     Tony       23       No
España    Albacete   Antonio    34       Si
España    Albacete   Pedro      32       No

I do not know how to include that field, which would be Boolean where 1 = yes and 0 = no Let's see if you can take a cable. Thanks

sql sql-server

asked by Miguel 13.02.2018 в 18:23

source

1 answer

Disable a combo when detecting that a text type input has data JavaScript Android Interface WebView Call Java function From JS onclick

score -1 · Answer 1

You could consider using ROW_NUMBER() to get the index of the rows grouped by the columns Pais , Ciudad and then sort them by the column Nombre .

;WITH TABLA AS (
    SELECT
        INDICE = ROW_NUMBER() OVER(PARTITION BY PAIS, CIUDAD ORDER BY NOMBRE),
        PAIS, 
        CIUDAD,
        NOMBRE
    FROM PERSONA
)
SELECT
    PAIS, 
    CIUDAD,
    NOMBRE,
    ES_PRIMERO = (
        CASE WHEN INDICE = 1 
            THEN 'SI' 
            ELSE 'NO'
        END
    )
FROM TABLA

DEMO