Select statement inside a select statement

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

Select statement inside a select statement

Author

Topic

young99
Starting Member

2 Posts

Posted - 2012-02-17 : 15:41:53

I am having an issue with using a MIN select statement inside a select. It pulls data but it is leaving out some data.

My statement is extremely long and this is the piece I am having the issue with.

where cor_address.CAL_UID_TYPE = '2'
and cor_address.stl_uid_state = state_mlkp.stl_uid
and cor_address.cor_rank = (select MIN(cor_address.cor_rank)
from cor_address
where cor_address.cal_uid_type = '2')

Sample data:

User 1 has:
CAL_UID_TYPE COR_RANK
2002 1
2 2

User 2 has:
CAL_UID_TYPE COR_RANK
2002 2
2 3
2000 5
2 1
1 4

The statement will run and will pull in user 2 correctly with CAL_UID_TYPE of 2 and COR_RANK of 1. It will omit user 1 and it should be getting CAL_UID_TYPE of 2 and COR_RANK of 2. I am thinking the min statement does see user 1 correctly.

If I pull this min statement out and put it by itself it works correctly outside of the large statement.

I am confused why they will run correct independently but not inside one another.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 15:54:38

so what should be the output you're expecting?

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

young99
Starting Member

2 Posts

Posted - 2012-02-17 : 16:05:00

I really was expecting to get both users brought back in the results

User1:
CAL_UID_TYPE COR_RANK
2 2

User2:
CAL_UID_TYPE COR_RANK
2 1

The problem is that User 1 is not coming back in the results. I was thinking using the min field it would see that 2 was the lowest number for COR_RANK because it is the only number with the CAL_UID = 2 criteria.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 16:08:28

difficult to tell why it didnt return user1 with info posted. it might be due to one of those join conditions not matching also

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources