Hacker News new | past | comments | ask | show | jobs | submit login

You can also express this example using the first_value window function:

   select distinct grp, first_value(id) over (partition by grp order by id) as rn from testing;



The only challenge here is that you can't bring out other fields from the table.

Building on my previous example:

    insert into testing values (8, 2, 'stuff');   
    select distinct grp, first_value(id) over (partition by grp order by id) as rn, other from testing;
This returns two rows for grp = 2, whereas adding the 'other' column to my first attempt (both in the CTE and the main query) works as expected and only returns one row for each grp.

This would work properly within the CTE, but then you're joining to the CTE instead of just querying it. There are many ways to solve this problem.

My ideal solution would be something like this that gets rid of the CTE:

    select grp, id, row_number() over (partition by grp order by id) as rn, other from testing where rn = 1
But that doesn't work. Oracle only allows window functions in the SELECT clause, not in the WHERE clause, plus you can't reference the column alias anyway.




Join us for AI Startup School this June 16-17 in San Francisco!

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: