Select 1 row

Question

Select 1 row

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
May 24, 2018 at 2:12 pm

#341731

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.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

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 *******

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 2

I want all the columns... This will only return Hdrid and tabname, right?

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

then just add * or column names

***The first step is always the hardest *******

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

GrassHopper - Thursday, May 24, 2018 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

Look at the code, partition is spelled partiton.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 6

Lynn Pettis - Thursday, May 24, 2018 3:27 PM
GrassHopper - Thursday, May 24, 2018 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
Look at the code, partition is spelled partiton.

Doesn't everyone spell it that way.... ? lol duh...
thanks!

Select 1 row

Cookies on SQLServerCentral