Finding highest and lowest values in a query 1

[nissan240zx]

Hello All,
I am referring to my previous post (

http://www.tek-tips.com/viewthread.cfm?qid=1157436&page=1).

I have reached this far on my quest

SELECT Minimum([EBCDIC_NAME]) AS [Minimum Value], Maximum([EBCDIC_NAME]) AS [Maximum Value], PaysysTag.HDR_USER_ALPHA_2, Collectors.Collector_FirstName, Collectors.Collector_LastName, Collectors.Collector_JobTitle, Collectors.Collector_Group
FROM Collectors INNER JOIN PaysysTag ON Collectors.Collector_ID = PaysysTag.CTA_PERM_COLL_ID;

When I run this I get all the records for that CTA_PERM_COLL_ID.
But all I want is the lowest number and the highest.
Any advice..
Thanks in advance..

A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[GHolden]

Hi, if I understand what you're trying to do, I think that the closest that you'll get to what you want: try using TOP 1, ORDER BY and UNION

E.g.

SELECT TOP 1 [EBCDIC], PaysysTag.HDR_USER_ALPHA_2, Collectors.Collector_FirstName, Collectors.Collector_LastName, Collectors.Collector_JobTitle, Collectors.Collector_Group
FROM Collectors INNER JOIN PaysysTag ON Collectors.Collector_ID = PaysysTag.CTA_PERM_COLL_ID
ORDER BY [EBCDIC]
UNION
SELECT TOP 1 [EBCDIC], PaysysTag.HDR_USER_ALPHA_2, Collectors.Collector_FirstName, Collectors.Collector_LastName, Collectors.Collector_JobTitle, Collectors.Collector_Group
FROM Collectors INNER JOIN PaysysTag ON Collectors.Collector_ID = PaysysTag.CTA_PERM_COLL_ID
ORDER BY [EBCDIC] DESC

This will return the first record when sorted by EBCDIC ascending i.e. the minimum of EBCDIC unioned with the first record when sorted by EBCDIC descending i.e. the maximum of EBCDIC. It will be returned as two records, not one as you specified, however, may be of some help.




There are two ways to write error-free programs; only the third one works.

 

[nissan240zx]

Hello G Holden,
I used your idea and came up with this

INSERT INTO FINAL_ALPHA_TBL ( Collector_FirstName, MinOfEBCDIC_NAME, FirstOfHDR_USER_ALPHA, MaxOfEBCDIC_NAME, LastOfHDR_USER_ALPHA, TotalAccounts, COLL_ID, TEAMNAME, Collector_LastName )
SELECT USER_ALPHA_TBL.Collector_FirstName, Min(USER_ALPHA_TBL.EBCDIC_NAME) AS MinOfEBCDIC_NAME, First(USER_ALPHA_TBL.HDR_USER_ALPHA) AS FirstOfHDR_USER_ALPHA, Max(USER_ALPHA_TBL.EBCDIC_NAME) AS MaxOfEBCDIC_NAME, Last(USER_ALPHA_TBL.HDR_USER_ALPHA) AS LastOfHDR_USER_ALPHA, Count(USER_ALPHA_TBL.Collector_FirstName) AS CountOfCollector_FirstName, USER_ALPHA_TBL.CTA_COLL_ID, USER_ALPHA_TBL.[Group Name], USER_ALPHA_TBL.Collector_LastName
FROM USER_ALPHA_TBL
GROUP BY USER_ALPHA_TBL.Collector_FirstName, USER_ALPHA_TBL.CTA_COLL_ID, USER_ALPHA_TBL.[Group Name], USER_ALPHA_TBL.Collector_LastName;

Its works now as desired.
Thanks for the method...!!!!
Have a grt weekend


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder