SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 how to select mutiple rows < 4
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jej1216
Starting Member

27 Posts

Posted - 09/26/2007 :  11:19:19  Show Profile  Reply with Quote
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

458 Posts

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

madhivanan
Premature Yak Congratulator

India
22742 Posts

Posted - 09/27/2007 :  02:50:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
30103 Posts

Posted - 09/27/2007 :  03:07:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22742 Posts

Posted - 09/27/2007 :  03:13:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
30103 Posts

Posted - 09/27/2007 :  03:20:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22742 Posts

Posted - 09/27/2007 :  03:53:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/27/2007 :  04:40:37  Show Profile  Reply with Quote
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

Edited by - eyechart on 09/27/2007 04:44:18
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30103 Posts

Posted - 09/27/2007 :  04:48:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/27/2007 :  04:55:41  Show Profile  Reply with Quote
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 - 09/27/2007 :  10:39:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000