Menu
I have a table with column a having not necessarily distinct values and column b having for each value of a a number of distinct values. I want to get a result having each value of a appearing only once and getting the first found value of b for that value of a. How do I do this in sql server 2000?
example table:
a b
1 aa
1 bb
2 zz
3 aa
3 zz
3 bb
4 bb
4 aa
Wanted result:
a b
1 aa
2 zz
3 aa
4 bb
In addition, I must add that the values in column b are all text values. I updated the example to reflect this. Thanks
979
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).
groupby. nth() function is used to get the value corresponding the nth row for each group. To get the first value in a group, pass 0 as an argument to the nth() function.
The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
The first way to find the first row of each group is by using a correlated subquery. In short, a correlated subquery is a type of subquery that is executed row by row. It uses the values from the outer query, that is, the values from the query it's nested into.
;with cte as
(
select *,
row_number() over(partition by a order by a) as rn
from yourtablename
)
select
a,b
from cte
where rn = 1
SQL does not know about ordering by table rows. You need to introduce order in the table structure (usually using an id column). That said, once you have an id column, it's rather easy:
SELECT a, b FROM test WHERE id in (SELECT MIN(id) FROM test GROUP BY a)
There might be a way to do this, using internal SQL Server functions. But this solution is portable and more easily understood by anyone who knows SQL.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
