jsbx
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
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.
Is your query working now, I can't tell from your last post if you still have an issue.
ASKER
Thank you very much.
Nevermind, just saw you close it!
Open in new window
Then from that, select the first instance of each item now that you have them numbered.
Open in new window