SQL D¡stinct/unique field problem

Dev Shed Forums Sponsor:

Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!

April 29th, 2004, 07:08 AM

juancliment

Registered User

Join Date: Apr 2004

Posts: 12

Time spent in forums: 2 h 47 m 24 sec
Reputation Power: 0

SQL D¡stinct/unique field problem

Hello everyone,

I need an SQL that works for the following, if anyone can help, ..,.

I want to get all the fields from a table where the field "IdCompany" is not repeated more than 3 times. Can this be one in a simple SQL?

IdCompany field values are like this: 5441, 1778, 8500, etc.

thanks!

April 29th, 2004, 08:05 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,762

Time spent in forums: 2 Weeks 4 Days 4 h 29 m 45 sec
Reputation Power: 37

Code:

select * from t where idCompany in (
select IdCompany 
from t
group by IdCompany
having count(*) <= 3)

April 29th, 2004, 08:44 AM

juancliment

Registered User

Join Date: Apr 2004

Posts: 12

Time spent in forums: 2 h 47 m 24 sec
Reputation Power: 0

Different SQL

Thank you, that is perfect, but I've realized I didn't express correctly wht I need to get, sorry..

The table contains products from different companies. I want to get all the products, but I just want to show 3 records (products) per company at most. There are companies with one product, and other with up to 100 products.

So the field IdCompany for a certain value could appear just 3 times.
thanks, J.

April 29th, 2004, 11:07 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,762

Time spent in forums: 2 Weeks 4 Days 4 h 29 m 45 sec
Reputation Power: 37

Code:

select * from product p
where idCompany in 
  (select top 3 idCompany 
    from product
   where productId = p.productId
   order by IdCompany desc)

Change names as apropriate.

April 29th, 2004, 03:41 PM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

So say there are 3 companies...

A, B, and C

...company A has 1 product, B has 3 products, and C has 10 products...in the return you want 7 total records...

Company A w/it's 1 product
Company B w/it's 3 products
Company C w/three of it's 10 total products

...correct??

this is something I put together real quick, but there are assumptions (so many that this is a far cry from a "solution", in fact it may give the word "solution" a bad name

)that could easily render it useless...they are...

1) Thre is an ID or recNum field...
2) It's steadily incremented...
3) There are no holes...(id = 4, 5, 7, 9, 10...possibly from deleted records)
4) based on the ID or recNum field, the company ID's along w/their products are all grouped together

Code:

select  * from Product P
join   (select  distinct IDcompany,
                min(productID) as MinID,
                min(productID) + 2 as MaxID
	from	Product
	group	by IDCompany) as A
on	P.IDCompany = a.IDCompany
where 	P.IDCompany between A.minIDcompany and A.MaxIDcopmany

...a more solid way using the same logic as above could be had by creating a temp table w/an Identity column...that may be stretching it though, I could give it to you if you'd like though...the above is way to thin to suggest actually using...it could work though. It's also assuming you want ALL companies returned, a much cleaner and WAY easier solution can happen if you only want to select 1 company @ a time and display it's products.

Code:

select  top 3 IDcompany, productID from Product
where   IDcompany = @IDCompany --enter ID of comp here

Last edited by Username=NULL : May 4th, 2004 at 06:37 PM.

May 4th, 2004, 06:32 PM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

something else I came up with...if you're using SQL server.

Code:

select	* from Product P
join	(select	CASE A.minID
		  When  Null then 'No company assigned'
		  Else 	(select	min(IDCompany) from Product
			 where	productID = A.minID)
		END [IDCompany],
		A.minID,
		min(P.productID) as midID,
		A.maxID
	 from	Product P
	 right	join
		(select  distinct IDcompany,
	             	 min(productID) as MinID,
		         max(productID) as MaxID
		 from	Product
		 group	by IDCompany) as A
	 on	P.IDCompany = A.IDCompany
		and P.productID <> A.minID and P.ID <> A.maxID
	 group	by P.IDcompany, 
		A.minID, 
		A.maxID) as A
on 	P.IDcompany = A.IDcompany
	and P.productID in (A.minID, A.midID, A.maxID)
order	by P.IDCompany,
	P.productID

Last edited by Username=NULL : May 5th, 2004 at 04:53 PM.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: