Re: SQL -Only the first of every row ?

"Ralf Bender" <ralf.bender_at_arcor.de> a écrit dans le message de news:4110c593$0$7311$9b4e6d93_at_newsread2.arcor-online.net...
> Hello,
>
> I got a little problem to solve. There is a table like this:
> objectnr, names, dates
> 123,data1,2004
> 123,data1,2003
> 234,data2,2003
> 456,data3,2004
> 456,data4,2004
>
> Now i want to select all rows which have 2004 in field dates, BUT i only
> want to see every objectnr once and all columns.
>
> The result should look like this:
> 123,data1,2004
> 456,data3,2004
>
>
> Not like this:
> 123,data1,2004
> 456,data3,2004
> 456,data4,2004
>
>
> I try it with distinct for objectnr, but then I must have all other
> columns.
> Could some help me here
>
> thx
> ralf

select objectnb, names, dates
from (
select objectnr, names, dates,
row_number() over(partition by objectnr order by names) rn from my_table
where dates=2004
)
where rn=1
/

--
Regards
Michel Cadot