Only one expression can be specified in the ...

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

Only one expression can be specified in the ...

Author

Topic

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 05:41:34

select list when the subquery is not introduced with exists.

I get this error when I run this script

select min(registration_no)as 'REG NO' , passport_no
from #attendances a
where passport_no <> ''
group by passport_no

union

select min(registration_no)as 'REG NO', passport_no
from #attendances
where
registration_no >(select min(registration_no) , passport_no
from #attendances a
where passport_no <> ''
group by passport_no)
and passport_no <> ''
group by passport_no

Can anyone see where I am going wrong! Thanks in advance for your help.

Sam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:48:20

select min(registration_no)as 'REG NO' , passport_no
from #attendances a
where passport_no <> ''
group by passport_no

union

select min(registration_no)as 'REG NO', passport_no
from #attendances
where
registration_no >(select min(registration_no) from #attendances a
where passport_no <> ''
group by passport_no)
and passport_no <> ''
group by passport_no

Madhivanan

Failing to plan is Planning to fail

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 05:56:44

Thanks madhivanan for your speedy reply. Unfortunately when I ran your code I got this error.....

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:03:03

What are you trying at the second part of union?

Madhivanan

Failing to plan is Planning to fail

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 06:05:43

I am trying to get the first instances of a training passport being used (shown by unique registration number) in the first part and then unioning it to the second instance of it being used in the second part. This works if I specify a passport number but not if I say where passport_no <> ''.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:10:05

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 06:11:33

2005. Still getting used to it!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:14:26

quote:
Originally posted by SamUK30

2005. Still getting used to it!

Refer point 3
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 06:21:53

OK I have to be honest... I am struggling with that. Are you saying thats a way of doing a row count that resets itself for every different passport number. That would be great as then I could select row 1 and 2 for each specific passport. I don't totally understand the code though. I really appreciate your help though!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:28:48

quote:
Originally posted by SamUK30

OK I have to be honest... I am struggling with that. Are you saying thats a way of doing a row count that resets itself for every different passport number. That would be great as then I could select row 1 and 2 for each specific passport. I don't totally understand the code though. I really appreciate your help though!

Yes. See if you get answer

Select * from
(
	select row_number() over(partition by passport_no order by passport_no) as row_number, * from #attendances
) T
where row_number<=2

Madhivanan

Failing to plan is Planning to fail

SamUK30
Starting Member

6 Posts

Posted - 2008-07-17 : 06:32:30

Madhivanan you are a star!

Thats it. Its perfect. Thanks you SO much for your help. If you ever offer any sql classes, let me know!

Sam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 06:37:29

quote:
Originally posted by SamUK30

Madhivanan you are a star!

Thats it. Its perfect. Thanks you SO much for your help. If you ever offer any sql classes, let me know!

Sam

You are welcome

If you are new to sql, read these
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources