Sql combine queries help
Thanks for taking the time to check this out... this is completely new stuff to me (teaching myself @ work)
Here is an example of a query that works for a single value i'm trying to obtain:
SELECT TOP 1 F_SGRP, F_TEST, F_VAL
WHERE F_TEST=1342169992 AND F_SGRP>%F_SGRP%
ORDER BY F_SGRP DESC
I'm filtering the table "SGRP_TST" by "F_TEST" ID Number and then grabbing the value "F_VAL" from the most recent record
I need helping doing the same exact thing but using several different "F_TEST" ID's and returing several different "F_VAL"s in a single query
Any help is greatly appreciated
Please ensure you post in the correct forum. You have posted in the MySQL forum, however you are not using MySQL as TOP is not supported by MySQL.
I'll move this to the general database forum.
Without knowing everything:
SELECT F_SGRP, F_TEST, F_VAL
WHERE F_TEST IN (comma delimited list of ids) AND F_SGRP>%F_SGRP%
ORDER BY F_SGRP DESC
What I don't understand is this:
And in the original query, why were you doing TOP 1 if the
But clearly WolfShade is on the right track. You need
But then the %F_SGRP% still makes no sense to me.
Thanks for the responses! ... Sorry about the mix up guelphdad
I'm going to try wolfshades suggestion and play around some more today... I'll get back to you
Hi Old Pedant... The F_Test #'s are not unique. In this case, one of the F_TEST numbers represent "accumulated dowtime" for a specific processing line. Another F_TEST number may represent the "accumulated uptime" for that specific process. These are accumulated values which is why I need to retrieve the most recent.
The DMS OLEDB Provider actually has a "Startup" command which produces a "Token" and a "value" ... (Again I don't fully understand what I'm doing but I've been somewhat successful.)
This is my "Startup"
SELECT TOP 1 F_SGRP
WHERE F_TEST=1342169960 ORDER BY F_SGRP ASC
So the %F_SGRP% is looking at the Startup. The "F_SGRP" increments with each record made. Startup is looking at the first F_SGRP (record) made with that F_TEST name... which is why the one in my "Processing" code has to find a one that is greater.
The reason for combining these queries is because I have only been able to do this for a single value in each DMS Provider. I am trying to avoid creating multiple DMS Providers as we've learned in the past that this can slow things down significantly and causes problems with our atuomated data collection.
Thanks again! Appreciate it!
I think I need to disable that "Startup" ... hmmm. I'm going to try things out today and leave an update
This is what I'm looking at
I've disabled "Startup" and removed the > variable part
Trying out the F_TEST IN (xxxxx, yyyyy) suggestion but can only return one value or in this case, the same value twice rather than both
oops... used back button in explorer / signed back in and duplicated last post by accident
If you use SELECT TOP 1 then you will ALWAYS only get back one result, no matter how many values are in your WHERE F_TEST IN ( ...list... )
What I don't see is how come that program shows you results of
when no place in the SQL query did you specify any "F_VAL1".
Maybe you should tell us what the point of all this is? What data you are trying to get?
Oh that F_VAL1 only appeared because I put "Select TOP 1 F_VAL, F_VAL"... The system just returned the value F_VAL from the first record it found twice and appended a "1" at the end of the second one to distinguish between variable names.
So... we are accumulating several different values through out the day each being recorded with a specific "F_TEST" tag ... The TOP 1 (last saved database value) is the only value with that "F_TEST" tag I care about.
At the end of each day from another system I am triggering an automated data collection. At this point I refer to "F_VAL" from one Provider and "F_VAL" from another Provider (each provider using a different "F_TEST" tag) and then perform a calculation.
In this case I am grabbing the total accumulated downtime (F_TEST=1342169960) on a production line for the day and dividing it by the total accumulated available time (F_TEST=1342169992) to determine the % Downtime for each day in order to trend
This worked successfully and I'm beinging to turn heads... Now my superiors would like me to expand this to every production Line. I will need more accumulated values which are being distinguished by different "F_TEST" tags. I am trying to avoid creating seperate providers for each "F_TEST" as I am sure it is unneccesary and it's already been suggested that we consolidate our providers as each one is checking in every second to see if the value has changed and this is extremely taxing on our systems resource
I really appreciate the help everyone... Getting Close!
This grabs all the Records containing the two test names I specified...
2726 Records right now
I only want to grab the most recent from each...
I may be able to configure something useing the startup to determine "F_SGRP"???
"F_SGRP" increments each time a record is saved. I only want the record containing the largest "F_SGRP" frome each of the "F_TEST" tags
Again, thanks a ton for helping me work through this
Sorry I keep posting one after the other but I'm getting excited now!
I Determined the most recent/largest "F_SGRP" value with the specified "F_TEST" tag in my STARTUP
In this case - the two values (Production Line 4 Accumulated downtime and Production Line 4 Accumulated available time) are saved at the same time so they share a "F_SGRP" Number
I've added the statement
and now i'm only returning the last record for each "F_TEST" Tag
This is progress as I will no longer need a separate provider for both "F_TEST" (Downtime and Available Time) on Line 4
But I'm not quite there yet as the "F_TEST" records for say Production Line 2 will not be saved at the same time as Production Line 4 so they will have a different "F_SGRP" value
That query, alone, will give you *EXACTLY* what it says: That maximum F_SGRP value *PER F_TEST* for all the F_TEST values given in that IN ( ... ) list.
I had to look up "SPC". "Statistical Process Control"?
And this InfinityQS isn't smart enough to give you a report such as you are after automatically? Makes me wonder how close to infinity it really is.
This gets me SO close... I will play some more to see if i can get the F_VAL associated with these records as well
Thank you so much for your help!
Using the last bit of code you supplied and some common sense I did some googling and discovered what I needed.
End Result looks like this:
SELECT T.F_SGRP, T.F_TEST, T.F_VAL
FROM SGRP_TST T
INNER JOIN (SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
WHERE F_TEST IN (1342169960, 1342169992)
GROUP BY F_TEST) Q
ON T.F_TEST = Q.F_TEST
AND T.F_SGRP = Q.MAXSGRP
Next step is to add more F_TEST to the query once I create them.
Big Ups to OLDPEDANT for sticking around to figure out which tools I was working with and my exact needs!
|All times are GMT +1. The time now is 09:01 PM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.