select one row per ID

问题:

I have following table:

180555  <id>404</id>    Meetjesland 404 2067    314
180555  <id>404</id>    Aalter      405 2067    404
504684  <id>2104</id>   Ballonvaart 723 2067    722
504684  <id>778</id>    Activiteit  1086 2067   313

I need to group this by ID (left column) so it should look like this

180555  <id>404</id>    Meetjesland 404 2067    314
504684  <id>2104</id>   Ballonvaart 723 2067    722

I mean, the second, third,... row with the same ID doesn't give any more information so it shouldn't showed in fact. I can't do dintinct because there are columns that have different values..

Thanks for any help

回答1:

This will choose one arbitrary row per column 1.
You don't get to choose which row you want

SELECT
   ...
FROM
    (SELECT
       *,
       ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY (SELECT 1)) AS rn
    FROM
       MyTable
    ) foo
WHERE
   rn = 1