Please help me with this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help me with this query

Author

Topic

rcoltrane
Starting Member

2 Posts

Posted - 2011-03-10 : 11:47:16

When running the SQL query below, It retrieves some lines that has the same ID_VAGA (ex. 386) but differents ID_DETALHE_STATUS. in this case I want to mantain only the line that has the higher ID_DETALHE_STATUS that would be 1047. How can I do this in the SQL code ??

This is the query:

Select VAGAS.ID_VAGA, TITULOS_VAGAS.TIT_VAGA, VAGAS.QTDE_VAGAS,
DETALHE_STATUS.ID_DETALHE_STATUS, DETALHE_STATUS.DETALHE_STATUS
From VAGAS Inner Join TITULOS_VAGAS On TITULOS_VAGAS.ID_TIT_VAGA = VAGAS.TIT_VAGA Inner Join DETALHE_STATUS On VAGAS.ID_VAGA = DETALHE_STATUS.ID_VAGA Order By VAGAS.ID_VAGA

And these are the results it's returning to me: But I want it to return only the higher ID_DETALHE_STATUS, that is 1047:

QTDE_VAGAS TIPO_VAGA VALOR_SALARIO TAXA_SELECAO TAXA_CANCEL UNIDADE ATIVO ID_DETALHE_STATUS DETALHE_STATUS
1 Estagiario 500,00 70 0 1 1 1047 A5
1 Estagiario 500,00 70 0 1 1 937 A2

Thanks for reading.
Roger.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-10 : 15:25:37

Assuming you are using SQL 2005 or later:

with CTE as (
Select VAGAS.ID_VAGA, TITULOS_VAGAS.TIT_VAGA, VAGAS.QTDE_VAGAS,
DETALHE_STATUS.ID_DETALHE_STATUS, DETALHE_STATUS.DETALHE_STATUS
,row_number() over (partition by VAGAS.ID_VAGA order by DETALHE_STATUS.ID_DETALHE_STATUS desc) as rowId
From VAGAS Inner Join TITULOS_VAGAS On TITULOS_VAGAS.ID_TIT_VAGA = VAGAS.TIT_VAGA Inner Join DETALHE_STATUS On VAGAS.ID_VAGA = DETALHE_STATUS.ID_VAGA 
) select * from CTE where rowId = 1 Order By VAGAS.ID_VAGA

rcoltrane
Starting Member

2 Posts

Posted - 2011-03-11 : 08:33:08

Thanks for your answer. However, it gives the following error:

ORA-00923: keyword FROM not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:
*Action:
Error in line: 4 Column: 98

I'm using it with SQL Developer and Oracle XE database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-11 : 08:37:37

Goto www.dbforums.com for asking Oracle questions.
This is a Microsoft SQL Server forum.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources