Help in a store procedure - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help in a store procedure

Author

Topic

marclas
Starting Member

16 Posts

Posted - 2012-08-08 : 10:38:15

Hi,
I have a Stro proc with many instructions among which this one [SQL] select cmp_id from CSR_COMPLEMENT
where DOS_ID = (select DOS_ID from CSR_DOSSIER where DOS_NODE = @DOS_NODE) order by cmp_numordre[/SQL]

if the number of row is 01, i can easily get the cmp_id, now how can i get the cmp_id when the number of row is more than one? in this case the cmp_id to take is the one with the higher cmp_numordre. from the result, I writing a new sql.

thanks

Marclas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:43:39

if there can be more than one rows replace = with IN


 select cmp_id from CSR_COMPLEMENT 
where DOS_ID IN (select DOS_ID from CSR_DOSSIER where DOS_NODE = @DOS_NODE) order by cmp_numordre

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-08-09 : 12:21:08

[code]
select cmp_id from CSR_COMPLEMENT
where DOS_ID = (select top 1 DOS_ID from CSR_DOSSIER where
DOS_NODE = @DOS_NODE order by cmp_numordre)
order by cmp_numordre
[/code]

Srinika

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 12:48:42

quote:

Originally posted by Srinika


select cmp_id from CSR_COMPLEMENT 
   where DOS_ID = (select top 1 DOS_ID from CSR_DOSSIER where   
                   DOS_NODE = @DOS_NODE order by cmp_numordre desc)
 order by cmp_numordre

Srinika

as definition says
the cmp_id to take is the one with the higher cmp_numordre

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources