Get only the first result select where in

Hello,

TMP_Codigos is full of unique codigo
INVENTARIO_clsdo has multiples descripcion for same codigo

I need to get only the first descripcion for each codigo

my code is this but doesnt work

SELECT   descripcion, codigo
from inventario_clsdo
where codigo IN (select codigo from TMP_Codigos)
order by codigo

example inventario_clsdo:
descripcion      codigo
pepe                  1
juan                    1
maria                 1
perro                 2
gato                   2
mono                3
pollo                 4
pato                  4

example table TMP_codigos:
codigo
1
2
3

I need to get:

1    pepe
2   perro
3    mono

mS sql.
thanks.
jsbxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
You can add a RowNumber to your table to find the first occurrence.
SELECT ROW_NUMBER() OVER (PARTITION BY codigo ORDER BY codigo) AS "row",*
FROM inventario_clsdo

Open in new window


Then from that, select the first instance of each item now that you have them numbered.
WITH CTE AS(
	SELECT ROW_NUMBER() OVER (PARTITION BY codigo ORDER BY codigo) AS "row",*
	FROM inventario_clsdo
)
SELECT * FROM CTE
WHERE row = 1

Open in new window

0
Scott PletcherSenior DBACommented:
To be really accurate, you'll need some column to "tell" SQL what the "first" description is; when you determine that, replace the "(select null)" with that column name

select descripcion, codigo
from (
    SELECT  descripcion, codigo, row_number() over(partition by codigo order by (select null) desc) as row_num
    from inventario_clsdo
) as x
where row_num = 1
order by codigo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsbxAuthor Commented:
Scott,

Table TMP_Codigos has 1962 records
and your query has 160278 results, and it should be 1962 since I need to get any matching descripcion for each codigo in TMP_Codigos
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jsbxAuthor Commented:
Dustin, I get an error with yours:
Msg 336, Level 15, State 1, Line 20
Incorrect syntax near 'CTE'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.
0
Dustin SaundersDirector of OperationsCommented:
Why are you using TMP_Codigos?  Did you modify Scott's SQL at all, because that sounds like something was joined.

Are you changing the SQL posted at all when you are running it?  That error would indicate you have other SQL involved can you paste the changes you've made?
0
Scott PletcherSenior DBACommented:
jsbx:

It's likely the "WHERE row_num = 1" condition is missing from my query..

For Dustin's query, add a semi-colon before the WITH:

*
;WITH CTE AS(
      SELECT ROW_NUMBER() OVER (PARTITION BY codigo ORDER BY codigo) AS "row",*
      FROM inventario_clsdo
)
SELECT * FROM CTE
WHERE row = 1
0
jsbxAuthor Commented:
You are right I don't need tmp_codigos.

Now I'm using this

select descripcion, codigo
from (
    SELECT  descripcion, codigo, row_number() over(partition by codigo order by (select null) desc) as row_num
    from inventario_clsdo where descripcion like '%ch-hs%'
) as x
where row_num = 1
order by codigo
0
Dustin SaundersDirector of OperationsCommented:
I'm guessing then the block of code you had on top was the select into TMP_Codigos-- as Scott mentioned you'd need to terminate that with a semicolon.

Is your query working now, I can't tell from your last post if you still have an issue.
0
jsbxAuthor Commented:
Thank you very much.
0
Dustin SaundersDirector of OperationsCommented:
Nevermind, just saw you close it!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.