Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Filter w/in 2 columns

    I need to do a query that selects the lowest value in column x where column y = some value. If result count of column x > 1 then, and only then, select the lowest value in column z. So, the bottom line is only one record for y=some value is shown. This record is first based on the lowest value of column x and if it's a tie, then look at column z's min value. I figured out how to select the lowest value in column x where column y = some value, but am unable to select the appropriate record in column z if more than one value exists in column x.
    Pseudo Code:
    Select * From myTable where columnY = someValue and columnX = (select min(columnX) From myTable where columnY = someValue)

    from here I can't just find the lowest value in column z (as I did for columnX) because if the lowest value in column z is not the lowest value in column x this is not what I want. I'd really appreciate any help, I'm using MS Access and have been trying to get this to work for quite some time...

  2. #2
    Join Date
    Jul 2003
    Posts
    81
    try this:
    PARAMETERS [Y Value?] IEEEDouble;
    SELECT Min(tblXYZ.XValue) AS MinXValue, Min(tblXYZ.ZValue) AS MinZValue
    FROM tblXYZ
    WHERE (((tblXYZ.YValue)=[Y Value?]))

  3. #3
    Join Date
    Aug 2006
    Posts
    3

    :eek: :eek: :eek:

    I tried the code suggested above but it is only selecting the one record in the entire table with the smallest value for DateReleases - Release Date and the smallest value for Destination. Here are the actual db names and methodology. I simplified it in the above explanation to make it easier to explain but I think that it didn't describe the problem sufficiently.

    SELECT Min([DateReleased]-[Release Date]) AS [DWELL TIME], Min(Releases.Destination)
    FROM [QC Final Release], Releases
    WHERE ([QC Final Release].[Lot#]=Releases.[Lot#]);

    I need it to give a list of all unique lot#'s and their corresponding Minimum Values for (Date Released - Release Date) and if 2 lot #'s share the minimum value then grab the record with the minimum value of destination. Since there can't be the same destination twice for the same lot # this should always only show unique lot #'s.

    Some examples:
    [lot#] [dwell time] [destination]
    1 5 M <--Picks, min(dwell time) where lot#=1
    2 4 C <--Picks, dwell time tied, C<M
    2 4 M <--Not Picked
    3 0 M <--Picks, min(dwell time) where lot#=3
    4 0 C <--Picks, min(dwell time) where lot#=4
    5 3 M <--Picks, min(dwell time) where lot#=5
    5 4 C <--Not Picked, b/c not min(Dwell time)

    Output shows one record for lot 1,2,3,4,5,etc, etc....

    Below is some code I tried to work out that is really, really messy and pretty absurd, it still doesn't do what I want it to, oh the pain and anguish I have gone through to try and make this work, I'm sure the solution is very simple...

    SELECT [QC Final Release].[Lot#], [QC Final Release].DateReleased, myTable.[Release Date], myTable.Destination, [DateReleased]-[Release Date] AS [Dwell Time] FROM [QC Final Release], Releases AS myTable WHERE ([QC Final Release].[Lot#]=[myTable].[Lot#] AND ([DateReleased]-[Release Date]) = (Select Min ([DateReleased]-[Release Date]) From [QC Final Release], Releases Where [QC Final Release].[Lot#] = Releases.[Lot#] And Releases.[Lot#] = myTable.[Lot#]) And ((1 = (SELECT Count(*) FROM [QC Final Release], Releases WHERE [QC Final Release].[Lot#]=Releases.[Lot#] And Releases.[Lot#]=myTable.[Lot#] And ([DateReleased]-[Release Date]) = (Select Min ([DateReleased]-[Release Date]) From [QC Final Release], Releases Where [QC Final Release].[Lot#] = Releases.[Lot#] And myTable.[Lot#] = Releases.[Lot#]))) OR ((1 < (SELECT Count(*) FROM [QC Final Release], Releases WHERE [QC Final Release].[Lot#]=Releases.[Lot#] And Releases.[Lot#]=myTable.[Lot#] And ([DateReleased]-[Release Date]) = (Select Min ([DateReleased]-[Release Date]) From [QC Final Release], Releases Where [QC Final Release].[Lot#] = Releases.[Lot#] And myTable.[Lot#] = Releases.[Lot#]))) And (myTable.Destination = (Select Min(Destination) From [QC Final Release], Releases Where Releases.[Lot#] = [QC Final Release].[Lot#] And myTable.[Lot#] = Releases.[Lot#] And ([DateReleased]-[Release Date]) = (Select Min ([DateReleased]-[Release Date]) From [QC Final Release], Releases Where [QC Final Release].[Lot#] = Releases.[Lot#] And myTable.[Lot#] = Releases.[Lot#]))))));

  4. #4
    Join Date
    Jul 2003
    Posts
    81
    Please tell me the data type of its field and also if you handle somehow the null values

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    the data types of both columns I'm trying to get min vals for are strings and nulls are not allowed. The min functions appear to work fine on each I just can't get the min of one column to take precedence over the min of the other. It might be important to also inform you that when there are duplicate min vals in the first column, the 2nd min column is unique at that point after all previous filtering. Eg: There CAN be multiple records with duplicate lot #'s and duplicate min vals for dwell time, but there CAN NOT be multiple records with duplicate lot#'s, duplicate min vals for dwell time, and duplicate destinations. It's kind of like a tree...

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think this will work:

    Create a query that selects y and the min of x. Results:

    y-----x
    100--3
    200--4
    200--4

    Then with those results create a query to count the instances. Results

    y----x----Count
    100--3----1
    200--4----2

    Then create another query to find the lowest z value for each y and x:
    y----x----z
    100--3---4
    200--4---2

    Then create a query that joins the last two results on y and x:
    y----x----Count---z
    100--3----1-------4
    200--4----2-------2

    Finally, create a query that selects x or z based on Count being > 1.

Posting Permissions

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