select one row per ID

Question

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

How to you decide which **one** of the two (or more) rows with identical ID to select? Based on what criteria?? (it would help if you showed **column names** in your data sample....) — marc_s, Dec 13 '11 at 08:48
Why choose row 2 (ends 404) over rows (ebd 314). Same for 722/313 pair? You want *any* random row? If so, this doesn't make sense — gbn, Dec 13 '11 at 08:49
Yes, the other information doesn't make sense for me. I just need one row (the first) for one ID — Ozkan, Dec 13 '11 at 08:49
What is the ordering, i.e. how do you know to save those 2 rows? — Ash Burlaczenko, Dec 13 '11 at 08:50

score 2 · Accepted Answer · answered Dec 13 '11 at 08:50

2

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

answered Dec 13 '11 at 08:50

gbn

422,506
82
585
676

I get 'over' is not supported. Is this for sql 2008? – Ozkan Dec 13 '11 at 09:02
@Ozkan: yes, it is. http://msdn.microsoft.com/en-us/library/ms189461.aspx Now, as mentioned before, the problem lies in how run/create the code. Fix that, don't complain http://stackoverflow.com/q/8477500/27535 – gbn Dec 13 '11 at 09:07

select one row per ID

1 Answers1

Linked