Selecting lowest value from multiple fields

[SmallTime]

Wonder if someone would be kind enough to help me out here.

How can I amend the following so to only select the profile with the lowest value, if a value exists?

Select

ProfileID,
Name,
Profile1,
Profile2,
Profile3,
Profile4

from TblSample


- Profiles are an integer between 1 - 9
- Profiles might be repeated so that 2 or more profiles could be the same number
- The fields aren't mandatory so some profiles might be null or empty.


Many thanks
SmallTime

 

[Taruz]

Hello..

This query lists all the profile fields in the most lowest value ones..:

select ProfileID, Name, tt
from (
   Select ProfileID, Name, Profile1 as tt  from TblSample
union
   Select ProfileID, Name, Profile2 from TblSample
union
   Select ProfileID, Name, Profile3 from TblSample
union
   Select ProfileID, Name, Profile4 from TblSample
union
   Select ProfileID, Name, Profile5 from TblSample
     )  as trz
where tt in (
     select min(tt) from (Select ProfileID, Name, Profile1 as tt  from TblSample
            union
            Select ProfileID, Name, Profile2 from TblSample
            union
            Select ProfileID, Name, Profile3 from TblSample
            union
            Select ProfileID, Name, Profile4 from TblSample
            union
            Select ProfileID, Name, Profile5 from TblSample)  as trt 
)

I hope you asked for this result..

 

[SmallTime]

Fantastic.

I'd never have managed that by myself. I've been trying all sorts of things for hours without luck.

Thank you
SmallTime

 

[SmallTime]

Hold on all the results in tt are coming out as 0!

I removed all reference's to Profile5 as there isn't a profile5. The query runs OK but the result column in tt are all 0's which I now see are being taken from those profiles that are empty (defaulting to 0). Hmmm. How could I exclude 0's?

 

[Taruz]

Hello again ..

this query take the smallest value other than 0 ...:

select ProfileID, Name, tt
from (
   Select ProfileID, Name, Profile1 as tt  from TblSample
union
   Select ProfileID, Name, Profile2 from TblSample
union
   Select ProfileID, Name, Profile3 from TblSample
union
   Select ProfileID, Name, Profile4 from TblSample
     )  as trz
where tt in (
     select min(tt) from (Select ProfileID, Name, Profile1 as tt  from TblSample
            union
            Select ProfileID, Name, Profile2 from TblSample
            union
            Select ProfileID, Name, Profile3 from TblSample
            union
            Select ProfileID, Name, Profile4 from TblSample

)  as trt 
where tt<>0 
)

 

[SmallTime]

Not quite there.

I've attached a couple of files to show the results of the original data (978 Rows) and the queried data (137 Rows) and All tt fields showing as 1.

I think I'll do the filtering in the front end (Access) as I'm much more comfortable with that.

Thanks for your time and care

SmallTime

 

[SmallTime]

Sorry I thought I'd attached the file but realised that RTF docs can't be uploaded. Here they are in TXT format (headings will be out of alignment)

 

[Taruz]

Hello ..

I thought you wanted the smallest value in the entire table of values​​.

I guess, do you want for each record separately.

I prepared a function. You can see the results of the query named query1. Greatest value and the lowest value of both

Sorry, my English is not very good ..

 

[SmallTime]

That's really great, exactly what I was looking for

Sorry for the misunderstanding. I was hoping to do this on server side but on this occasion I think I'll take the performance hit and do it in access.

I like your MaxProfile & MinProfile functions, very cleverly crafted indeed.

A truly well deserved thanks.
SmallTime

 

[Taruz]

I'm glad you like it.

I forgot the server..

If you want, the same result, for the server, not using the function, the query can do it with..:

select 
   OriginalData.ProfileID, 
   Name, 
   Profile1, Profile2, Profile3, Profile4, 
   MINP
from OriginalData left join 
 (
select ProfileID, min(tt) as MINP
    from (select ProfileID, tt
           from (select ProfileID, Profile1 as tt
                  from OriginalData

      union
              select ProfileID, Profile2
              from OriginalData

      union
              select ProfileID, Profile3
              from OriginalData

      union
              select ProfileID, Profile4
              from OriginalData)  as aa
where tt<>0)  as bb
group by ProfileID
) as trz

 on OriginalData.ProfileID = trz.ProfileID

Greetings..

 

[SmallTime]

What can I say, that's the most comprehensive help I've ever received I would have been overjoyed with just half of what you done.

Thank You
SmallTime