July 1, 2010 at 11:19 am
Brandie Tarvin (7/1/2010)
What is vvers_dat supposed to be? I'm running your code on my sandbox and I can't do the data insert because that column disallows NULLs and has no insert value in your test data.
Sorry, i overlooked this.. vvers_dat is a column that is filled through a procedure which does getdate(). it does nothing special for the thing i try to do
July 1, 2010 at 1:10 pm
lionwelp (7/1/2010)
...Thanks for replying 🙂
You're correct, the validation got the most recent Druk_nr and lowest number of Status (like isbn 3095561991134 got 2 rows, one with druk_nr= 1,status=3 and druk_nr=5,Status=2. the one i need in this case would be the one with druk_nr=5,status=2)
The code would be great, however like i said to Mike01; the 'big picture' is to encapsulate an insert over this to copy the distinct results into a new table, which i don't see how i could do that with a CTE.
...
By changing order in PARTITION...OVER clause you can change the preference of status selection with highest or lowest value in Volgorde (or anything else). Currently it takes one with highest Volgorde. You can change DESC to ASC and you wil get the record with lowest one.
Now, you can easely use CTE in INSERT as per the following example:
;WITH someCTE
AS
(
SELECT Col1, Col2 FROM Table1 ....
)
INSERT INTO Table 2 (ColA, ColB)
FROM someCTE ...
You can use it in joins as well.
If you need records from CTE to be used in multiple queries, insert all from CTE into temp table:
SELECT * INTO #persistenCTE:-D FROM someCTE WHERE....
Please note: You can see I haven't used your UDF, you can find some discussion about this here: http://www.sqlservercentral.com/Forums/Topic945960-391-1.aspx
and here:
http://www.sqlservercentral.com/Forums/Topic945210-392-1.aspx
July 2, 2010 at 9:24 am
lionwelp (7/1/2010)
Brandie Tarvin (7/1/2010)
Also, am I reading correctly that you want the most recent Druk_nr value? The one with the greatest (most recent) creatie__dat?I want to have the most recent druk_nr and the one with the lowest number of the Status.ID table
There's a minor problem with the way you state your needs. It seems to me that you can get one or the other. Either the lowest status per ISBN (and the Druk_nr associated with that) or the most recent Druk_nr per ISBN (and the status associated with that). But if you want both the lowest status and the highest Druk_nr, all the code examples we've given you are both wrong and insufficient for your needs. So is the code you're working with.
Your example is:
lionwelp (7/1/2010)
You're correct, the validation got the most recent Druk_nr and lowest number of Status (like isbn 3095561991134 got 2 rows, one with druk_nr= 1,status=3 and druk_nr=5,Status=2.
But what if you have an additional data for this ISBN that includes Druk_nr = 3 and status = 1?
The status is the lowest, but the druk_nr is NOT the highest. And if you want the highest druk_nr (5) and the lowest status (1) in the same result set, none of the code we've given you will assist with that. In fact, if you have repeatable druk_nr values (i.e., druk_nr = 5, status = 4), you'll also have to change the code to account for those.
So make sure you know your data very well before you start implementing code and make sure you understand exactly how what you say you need is different from what will actually display if the data is different from the results you gave us.
July 26, 2010 at 7:22 am
The status is the lowest, but the druk_nr is NOT the highest. And if you want the highest druk_nr (5) and the lowest status (1) in the same result set, none of the code we've given you will assist with that. In fact, if you have repeatable druk_nr values (i.e., druk_nr = 5, status = 4), you'll also have to change the code to account for those.
It has been a while i posted in my thread, this is because i was pulled off this particular project, but now i'm back on it.
To get back on track, Brandy Tarvin is completely correct in what he says.
I didn't have much feedback about the data, but it is indeed insufficient with the code examples to be able to pull the correct data out of it.
It is a matter of priority, first and foremost is it the lowest Status, second is highest druk_nr (there is also a date/time, but i guess that's just further prioritising accordingly).
I already got a bit further into the db-flow, however i'm a bit stuck at the moment because of the correct syntax i need to use to pull out the correct data according to this latest description.
kind regards
July 26, 2010 at 8:10 am
lionwelp (7/26/2010)
It is a matter of priority, first and foremost is it the lowest Status, second is highest druk_nr (there is also a date/time, but i guess that's just further prioritising accordingly).
Do the druk_nr and status actually have to come from the same record? Or do you just want what the lowest status was at any time and the highest druk_nr at any time?
July 26, 2010 at 10:03 am
Brandie Tarvin (7/26/2010)
lionwelp (7/26/2010)
It is a matter of priority, first and foremost is it the lowest Status, second is highest druk_nr (there is also a date/time, but i guess that's just further prioritising accordingly).Do the druk_nr and status actually have to come from the same record? Or do you just want what the lowest status was at any time and the highest druk_nr at any time?
They are both in the same record, i need to get the priority's at any time, so what you mentioned as example :
druk_nr Status
---------------
1 3
5 2
3 1
It is druk_nr:3, Status:1 because the highest priority is Status(lowest number)
However, would there be an entry of druk_nr:4,Status:1 be added, the druk_nr would be prioritised between de 2 choices (the other would be 3,1)
Hope i make sense here 🙂
And tnx for revisiting this !
July 26, 2010 at 12:08 pm
lionwelp (7/26/2010)
They are both in the same record
Okay, this goes back to my second to last post. Will the data ALWAYS be that way?
If not, you're out of luck. You can't do what you're asking. You have to pick one or accept the fact that you need to get the values from two different records.
If the highest druk_nr is always associated with the lowest status, then just look for one or the other, not both, and you'll be covered.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply