December 6, 2007 at 2:41 pm
And, how do you think it is sorted?
_____________
Code for TallyGenerator
December 6, 2007 at 2:49 pm
SQL33 (12/6/2007)
That is a BIG IF!!! LOL. But... seriously, it needs to sort by only the sequence number for each customer link, withOUT the cuslink.
That's the point isn't it?
3 is less than 21 which is less than 22. All of which according to your own test data, are attached to 5432. The fact that you put that record lower down on the page doesn't matter.
If that's not what you want - then describe it differently - vladan's code is correct from what I can tell.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 2:50 pm
Sergiy (12/6/2007)
And, how do you think it is sorted?
I agree with Sergiy. I think about the physical order data appears in the table.
December 6, 2007 at 2:52 pm
johnsql (12/6/2007)
Sergiy (12/6/2007)
And, how do you think it is sorted?I agree with Sergiy. I think about the physical order data appears in the table.
screw the physical order - I'm looking at the HAVING.
it's going to pull out the third one in mathematical order by custlink.
Besides - don't count on the data being pulled in physical order.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 2:54 pm
I agree with Sergiy. I think about the physical order data appears in the table.
There is no such thing as "physical order data appears in the table".
Physical order of data in a table is purely coincidental.
_____________
Code for TallyGenerator
December 6, 2007 at 2:58 pm
As was initially requested (....I want to query the data shown below in the first dataset to return each entire row containing the nth lowest sequence number for each cuslink)
what is needed is the Nth lowest sequence number within each custlink.
clearly if your code includes the custink as part of the calculation to determine 'nth', you are not returning the correct sequence number, but are returning the correct custlink + sequence number, which is not the goal.
December 6, 2007 at 3:10 pm
SQL33 (12/6/2007)
As was initially requested (....I want to query the data shown below in the first dataset to return each entire row containing the nth lowest sequence number for each cuslink)what is needed is the Nth lowest sequence number within each custlink.
clearly if your code includes the custink as part of the calculation to determine 'nth', you are not returning the correct sequence number, but are returning the correct custlink + sequence number, which is not the goal.
There seems to be a failure in communication. When you say nth lowest, do you mean nth' in the PHYSICAL ORDER in the data as you represented? because to me and vladan, nth LOWEST sequence means, SORT the sequences for each custlink and pick the n'th one.
Also - are you treating these sequences as string values (as a string '3'>'21') and NOT numbers (as a number 3<21).
If you're looking for something using the physical order, then you're in trouble, because there are no guarantees that data will ever be returned in the physical order. No you CAN use something like an identity field to get there, but your table would need that set up, and you'd need to force the inserts to happen in the physical order you wish.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 3:14 pm
SQL33 (12/6/2007)
As was initially requested (....I want to query the data shown below in the first dataset to return each entire row containing the nth lowest sequence number for each cuslink)what is needed is the Nth lowest sequence number within each custlink.
clearly if your code includes the custink as part of the calculation to determine 'nth', you are not returning the correct sequence number, but are returning the correct custlink + sequence number, which is not the goal.
clearly if your code includes the custink as part of the calculation to determine 'nth', it will return the Nth lowest sequence number within each custlink.
Exactly what is needed
_____________
Code for TallyGenerator
December 7, 2007 at 1:01 am
SQL33, johnsql,
as several people mentioned, there is no way to say what is sequence number of a row, if you don't specify ORDER BY.
Rows in a table don't have any implicit ordering, you can't write a query that will return "first row" without specifying ORDER BY. You can only write a query that will return ONE row, but every time you run the query it can be a different row (if no data are added or modified between runs, it will often be the same row - but not necessarily).
December 7, 2007 at 8:35 pm
I think perhaps a better explanation would be helpful ... each custlink can and does exist in multiple rows, each in turn has a field containing a unique sequence number. I want to pick the nth lowest existing sequence number for each particular custlink. No sorting or ordering is necessary .. just a select retrieving the nth sequence number for each custlink.
December 8, 2007 at 6:04 am
No sorting or ordering is necessary .. just a select retrieving the nth sequence number for each custlink.
Man, sequence exists only on ordered arrays.
If there is no order then you cannot define any sequence.
No order array is a heap.
Imagine several heaps of stones.
If I ask you to pick 3rd stone from each heap - can you do it without any order specified?
_____________
Code for TallyGenerator
December 8, 2007 at 9:15 am
If each rock in each pile has a sequence number on it .... would you not then know how to arrange each group, and once arranged which one is in which order... which is the 2nd, 3rd, 20th, etc?
December 8, 2007 at 2:37 pm
SQL33 (12/8/2007)
If each rock in each pile has a sequence number on it .... would you not then know how to arrange each group, and once arranged which one is in which order... which is the 2nd, 3rd, 20th, etc?
So, you want to arrange heaps in order of sequence number.
That means, you cannot tell which is 2nd or 3rd without specifying the order.
Sequence number does not appear on a rock by itself. Somebody must write it when placing a rock in a pile.
Then you must specify which sequence number to be 1st - lowest, highest?
And only then you can find which stone is 2nd, which is 3rd.
Now, get back to the example and find which row is 3rd in each group according to the sequence number order.
_____________
Code for TallyGenerator
December 10, 2007 at 12:54 am
SQL33 (12/7/2007)
I think perhaps a better explanation would be helpful ... each custlink can and does exist in multiple rows, each in turn has a field containing a unique sequence number. I want to pick the nth lowest existing sequence number for each particular custlink. No sorting or ordering is necessary .. just a select retrieving the nth sequence number for each custlink.
Well, then the solution I posted earlier does precisely what you request.
Retrieves for each cuslink the row with Nth lowest sequence number. If you don't agree with me, please show me an example where it does not return correct result.
P.S. : The example posted before (that it does not return correct result for @n=3) is incorrect. Look up the rows with cuslink 5432. There are 4 such rows, with sequence 21, 22, 23 and 3. The row with sequence 3 is being inserted last, but of course 3 < 21... so with @n=1 it returns the row with sequence 3, with @n=2 row with sequence 21, with @n=3 row with sequence 22. What's wrong with that? (BTW, Matt also posted that it may depend on datatype of "sequence" column - if it should be string type, then result is different... you didn't reply to that.).
December 10, 2007 at 8:19 am
Well... when I run it today it does work properly, but I'm pretty confident that when I tested it after reading the post warning that it didn't work, that I received the same faulty results he did in my results. Nonetheless, it appears to work fine this morning.
Again, thanks for your help!
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply