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?
 
Scott PletcherConnect With a Mentor Senior 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
 
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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 SaundersConnect With a Mentor Director 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.