Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question 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
    FROM SGRP_TST
    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

    Thanks

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • Users who have thanked guelphdad for this post:

    unscarred (07-24-2012)

  • #3
    Regular Coder
    Join Date
    Apr 2012
    Location
    St. Louis, MO
    Posts
    985
    Thanks
    7
    Thanked 101 Times in 101 Posts
    Without knowing everything:

    SELECT F_SGRP, F_TEST, F_VAL
    FROM SGRP_TST
    WHERE F_TEST IN (comma delimited list of ids) AND F_SGRP>%F_SGRP%
    ORDER BY F_SGRP DESC
    ^_^

    If anyone knows of a website that can offer ColdFusion help that isn't controlled by neurotic, pedantic jerks* (stackoverflow.com), please PM me with a link.
    *
    The neurotic, pedantic jerks are not the owners; just the people who are in control of the "popularity contest".

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,986
    Thanks
    79
    Thanked 4,432 Times in 4,397 Posts
    What I don't understand is this:
    Code:
    F_SGRP>%F_SGRP%
    What is that supposed to be??? That's not legal in any variety of SQL that I know of.

    And in the original query, why were you doing TOP 1 if the F_TEST=1342169992 would have selected only one record in the first place?

    But clearly WolfShade is on the right track. You need F_TEST IN (xxxx,yyyy,zzzz)

    But then the %F_SGRP% still makes no sense to me.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

    What I don't understand is this:

    Code:
    F_SGRP>%F_SGRP%
    My fault. I apologize for not disclosing all the details. ... So I am working with an application called InfinityQS. We are running a Data Management System Provider Manager (part of the InfinityQS package) on the server to collect automated data.

    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
    FROM SGRP_TST
    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!

  • #6
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think I need to disable that "Startup" ... hmmm. I'm going to try things out today and leave an update

  • #7
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  • #8
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    oops... used back button in explorer / signed back in and duplicated last post by accident
    Last edited by unscarred; 07-24-2012 at 04:13 PM. Reason: duplicate post

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,986
    Thanks
    79
    Thanked 4,432 Times in 4,397 Posts
    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
    F_VAL 204.7
    F_VAL1 204.7
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  • #11
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

    ... hmmm

    Again, thanks a ton for helping me work through this

  • #12
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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 F_SGRP=%F_SGRP%
    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

    Getting Close

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,986
    Thanks
    79
    Thanked 4,432 Times in 4,397 Posts
    "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
    Time to learn a little more about SQL.
    Code:
    SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
    FROM SGRP_TEST
    WHERE F_TEST IN ( ...any length list of numbers ... )
    GROUP BY F_TEST
    ORDER BY F_TEST
    I still don't understand what your "startup" is for.

    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    unscarred (07-25-2012)

  • #14
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Time to learn a little more about SQL.

    Code:
    SELECT F_TEST, MAX(F_SGRP) AS MAXSGRP
    FROM SGRP_TEST
    WHERE F_TEST IN ( ...any length list of numbers ... )
    GROUP BY F_TEST
    ORDER BY F_TEST
    Thank you for this

    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

    I still don't understand what your "startup" is for.
    I believe the only purpose of the start-up is to provide a variable for use with conditions in the processing code

    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.
    In Infinity's defense, my company is pushing the limits with downtime and all this automated data... We have the software "communicating" with the PLC that our processing machines are connected to, to create automated downtime event records. Infinity is most commonly used for Quality Control and Specification Compliance. Most users never even use the DMS App and Providers. The main InfinitySPC application excels at custom user/gauge input screens and generating charts based off the collected information. It would be nice if there was a pre-canned way to calculate the percent for us but they've been good about listening to customer (our) needs and releasing new features with software revisions, so I can't complain about them.

  • #15
    New Coder
    Join Date
    Jul 2012
    Posts
    12
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Smile

    Got it!!!

    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
    FROM SGRP_TST
    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!

    Thanks Again!


  •  
    Page 1 of 2 12 LastLast

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •