cswebdev
asked on
SQL table "unique" rows
table 1:
ip# | date | state
--------------------------
1 | 8/8/2000 1:00pm | CA
1 |8/8/2000 2:00pm | CA
1 | 8/8/2000 3:00 pm | IN
1 | 8/8/2000 4:00 pm | IN
--------------------------
the data in table looks like above. What I want to do is create a new table having the same structure, but I want to insert only the first instance of each hits in a state in the given day i.e the new table should look:
ip# | date | state
--------------------------
1 | 8/8/2000 1:00pm | CA
1 | 8/8/2000 3: pm | IN
--------------------------
Time is required and I don't want to use cursor-- it has millions of rows.
ip# | date | state
--------------------------
1 | 8/8/2000 1:00pm | CA
1 |8/8/2000 2:00pm | CA
1 | 8/8/2000 3:00 pm | IN
1 | 8/8/2000 4:00 pm | IN
--------------------------
the data in table looks like above. What I want to do is create a new table having the same structure, but I want to insert only the first instance of each hits in a state in the given day i.e the new table should look:
ip# | date | state
--------------------------
1 | 8/8/2000 1:00pm | CA
1 | 8/8/2000 3: pm | IN
--------------------------
Time is required and I don't want to use cursor-- it has millions of rows.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
missed the 'state'
;with cte as (
select *, rn = row_number() OVER ( partition by convert(varchar, [date],112), [state] order by [date] asc )
from urtable )
SELECT [ip#], [date], [state]
from cte
where rn = 1
;with cte as (
select *, rn = row_number() OVER ( partition by convert(varchar, [date],112), [state] order by [date] asc )
from urtable )
SELECT [ip#], [date], [state]
from cte
where rn = 1
ASKER
awesome answer!!
select *, rn = row_number() OVER ( partition by convert(varchar, [date],112) order by [date] asc )
from urtable )
SELECT [ip#], [date], [state]
from cte
where rn = 1