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 2000 Forums
 Transact-SQL (2000)
 how to select mutiple rows < 4

Author  Topic 

jej1216
Starting Member

27 Posts

Posted - 2007-09-26 : 11:19:19
I need to do a select on a table to return those BUSINESS_UNIT/ASSET_ID's that have three rows or less of data. I'm having trouble with the statement.
I have tried:

select count(*), BUSINESS_UNIT, ASSET_ID
from FS.dbo.PS_BOOK
having count(*)< 4
group by BUSINESS_UNIT, ASSET_ID
order by BUSINESS_UNIT, ASSET_ID, count (*)

But get the error "Incorrect syntax near the keyword 'group'."

I know the "count" cannot be in a where clause, but I'm stuck for what else to try.

It's probably easier than I'm making it.

TIA,

jej1216

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-26 : 11:25:48
"having" goes after the "group by" and before the "order by".
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 02:50:00
Only WHERE should come before group by and not HAVING

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 03:07:20
I am happy things worked out for you, "joe".
But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 03:13:23
quote:
Originally posted by Peso

I am happy things worked out for you, "joe".
But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax?



E 12°55'05.25"
N 56°04'39.16"



He might think he could get answer quickly here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 03:20:54
Yes, maybe.
But he also posted the same question on at least one other site...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 03:53:39
quote:
Originally posted by Peso

Yes, maybe.
But he also posted the same question on at least one other site...



E 12°55'05.25"
N 56°04'39.16"



Then OP follows FIFO

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 04:40:37
quote:
Originally posted by Peso

I am happy things worked out for you, "joe".
But waht goes into your mind thinking you get a faster answer posting here than open Books Online and read about SELECT FROM syntax?



if you are new to SQL sometimes it is hard to figure out what BOL is trying to tell you. It can be confusing because of all the arguments and such that are available to you in a SELECT statement.

the OP has asked a good question imho. he has posted what he wants to do, as well as his code that is not working. He has made a simple mistake in syntax and several people have helped. this is exactly what sqlteam is for.

Remember we were all new to SQL at some point..



-ec
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 04:48:33
Yes I know. He got excellent help.

The point I am trying to make, is this:

Is the time spent posting this question on several forums more worth than open Books Online directly?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-27 : 04:55:41
quote:
Originally posted by Peso

Yes I know. He got excellent help.

The point I am trying to make, is this:

Is the time spent posting this question on several forums more worth than open Books Online directly?




I understand what you posted peter! My point is maybe the OP read BOL but didn't understand the SELECT statement syntax shown in BOL. We are not all SQL geniuses :)


-ec
Go to Top of Page

jej1216
Starting Member

27 Posts

Posted - 2007-09-27 : 10:39:34
Thanks for your help.

I did search the SQL Server Transact help files, and did not find the answer. I always do this before I post. As far as posting to another forum at the same time, I apologize but I was under the gun as I'm sure everyone can relate to.

My company is small, has no DBA, and many times we have to scramble for answers.

I greatly appreciate the help I get from forums such as this.

jej1216
Go to Top of Page
   

- Advertisement -