May 24, 2018 at 2:12 pm
I need to get 2 rows from a set of 11 records (highlighted in excel sample). I have millions of rows and more columns...but the key columns are HdrID + TabName. I want 1 of the rows per HdrID + TabName. See attached sample data.
May 24, 2018 at 2:17 pm
bung your query into a CTE and use Row_number() over partition
With CTE
as (
select HdrID , TabName, rown_number() over (partiton by HdrID , TabName order by HdrID) as RN
from tablename
)
select * from cte
where rn=1
***The first step is always the hardest *******
May 24, 2018 at 2:24 pm
I want all the columns... This will only return Hdrid and tabname, right?
May 24, 2018 at 2:38 pm
then just add * or column names
***The first step is always the hardest *******
May 24, 2018 at 3:12 pm
error msg:
Incorrect syntax near 'partition'
With CTE
as (
select *, row_number() over (partiton by HdrID , TabName order by HdrID) as RN
from xlshdr_EC
)
select * INTO dbo.xlshdr_EC_deduped from cte
where RN = 1
May 24, 2018 at 3:27 pm
GrassHopper - Thursday, May 24, 2018 3:12 PMerror msg:
Incorrect syntax near 'partition'With CTE
as (
select *, row_number() over (partiton by HdrID , TabName order by HdrID) as RN
from xlshdr_EC
)
select * INTO dbo.xlshdr_EC_deduped from cte
where RN = 1
Look at the code, partition is spelled partiton.
May 24, 2018 at 3:29 pm
Lynn Pettis - Thursday, May 24, 2018 3:27 PMGrassHopper - Thursday, May 24, 2018 3:12 PMerror msg:
Incorrect syntax near 'partition'With CTE
as (
select *, row_number() over (partiton by HdrID , TabName order by HdrID) as RN
from xlshdr_EC
)
select * INTO dbo.xlshdr_EC_deduped from cte
where RN = 1Look at the code, partition is spelled partiton.
Doesn't everyone spell it that way.... ? lol duh...
thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply