Returning a set number of unique elements..

[kevina]

I've got a problem and my limited SQL knowledge it now being tested, and I'm passing this on to you smart folks.. (Oracle DB)..

I have a table with data similar to this where the columns are ORDERID, SALESPERSON, PRODUCTID

123, TIM, PRODUCTA
123, TOM, PRODUCTA
123, MARY, PRODUCTB
124, JOE, PRODUCTA
125, TIM, PRODUCTA
125, TIM, PRODUCTC
126, JOE, PRODUCTA
127, TIM, PRODUCTB
127, TIM, PRODUCTA
.
.

And it's going to be huuuuuuuuuuuge.. So I have this need to run a query which would only match at a maximum on X (let's say X=2) unique order ids returned...

So if I did a search for all ORDERID < 500 I'd get something like:

123, TIM, PRODUCTA
123, TOM, PRODUCTA
123, MARY, PRODUCTB
124, JOE, PRODUCTA

I know that I always wouldn't get the 123 and 124 results, that's not important it's just important that the number of unique ORDERIDs found matching my search is limited to X (or 2) hits..

hints?

 

[tlbroadbent]

This query works in SQL Server. It should work in Oracle but you may have to tweak it. Sorry, but I don't kow Oracle.

Select OrderID, SalesPerson, ProductID
From Orders o
Where SalesPerson In
(Select Top 2 SalesPerson
From Orders
Where OrderID=rderID
Order By SalesPerson)
Order By OrderID, SalesPerson, ProductID Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[MikeJones]

IS this still an issue??? If you convert the above query to Oracle it may run very slowly due to the correlated sub query and trying to recreate the MS top function.

I was going to write another query you could try but I'm not sure of your requirements. Would your searches look like. WOuld you want to have ranges?? i.e. orderid between &quot;x&quot; and &quot;y&quot;. Would the search always be on order ID.

Mike.

 

[kevina]

Is it an issue? Well I'd like an SQL version.. I've done a work around in the code that calls the SQL (I just fetch the first X unique order ID rows returned)..

Yes, I'd like ranges..

No, the search wouldn't always be on OrderID.. For example, I'd like to search for all orders related to &quot;tim&quot; and return this:

123, TIM, PRODUCTA
123, TOM, PRODUCTA
123, MARY, PRODUCTB
125, TIM, PRODUCTA
125, TIM, PRODUCTC

 

[kevina]

err.. i mean this

No, the search wouldn't always be on OrderID.. For example, I'd like to search for all orders related to &quot;tim&quot; and return this:

123, TIM, PRODUCTA
125, TIM, PRODUCTA
125, TIM, PRODUCTC