Selecting records based on priority, and then selecting just one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting records based on priority, and then selecting just one

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-14 : 08:16:58

Christian writes "I'm working on a query (view) that selects one distinct record from a table of applications per Applicant (APPL_APPLICANT) based on a calculated priority number. Currently, my query is able to return an application with the highest priority number (PRIORITY_CODE). However, an Applicant might have two applications that have the same priority number. When this happens, my query returns the two applications with the same priority number, instead of just returning one (because they both have the same priority number).

Unfortunately, I need just exactly one record (just one highest priority number) so I can join the priority number with another table about the Applicant. Here is my current query:

SELECT     A.APPL_APPLICANT AS id, A.APPL_START_TERM AS start_term, A.APPL_CURRENT_STATUS AS app_stat, C.PCCOUNT, A.PRIORITY_CODE
FROM         [APPLICATIONS] AS A
LEFT join   (select  DISTINCT APPL_APPLICANT,
                max(PRIORITY_CODE) as PRIORITY_CODE,
                count(DISTINCT PRIORITY_CODE) AS PCCOUNT
        from    [APPLICATIONS]
        group   by PRIORITY_CODE, APPL_APPLICANT) as C     
ON A.[APPL_APPLICANT] = 	C.[APPL_APPLICANT]
GROUP BY A.APPL_CURRENT_STATUS, A.APPL_APPLICANT, A.APPL_START_TERM

There is only one table named APPLICATIONS:

APPLICATION_ID (key)
APPL_APPLICANT      - (Applicant ID)
APPL_START_TERM
APPL_CURRENT_STATUS
PRIORITY_CODE

SQL Server 2003 (8.00.730) SP2 on Windows 2003 Server

Can you offer any help or guidance? I really appreciate it. Thank you!

Christian Stuck
stuckc at rider dot edu"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 08:35:06

you can set a distinct on the priority_code...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 09:24:30

1) lose the LEFT OUTER JOIN. it should be an INNER JOIN in these types of queries. you are joining the same table to itself, w/o filtering -- it is guaranteed to always match up.

2) your inner query makes no sense -- you have a DISTINCT clause in a grouping query, and you are grouping on the same column you are taking the max of. If the specs you have mentioning in your opening paragraph are accurate, the inner select should be written like this:


select APPL_APPLICANT, MAX(PriorityCode) as MaxPriorityCode
from APPLICATIONS
group by APPL_APPLICANT

3) in your outer query, i don't see why you are doing a GROUP BY and it doesn't appear that you are grouping on enough columns to make the SQL statement work anyway.

Now, if an applicant can have two rows with the same priority code, then you just need to nest things in 1 more layer to take the MIN(APplication_ID):


SELECT APPL_APPLICANT, MIN(Application_ID) as ID
FROM
   APPLICATIONS A
INNER JOIN
  (above SQL) B
ON 
  A.APPL_APPLICANT = B.APPL_APPLICANT AND
  A.PriorityCode = B.MaxPriorityCode
GROUP BY APPL_APPLICANT

Note that the above returns the lowest ID for the max priority code per applicant. Just take the results of that and join it to the APPLICATIONS table one more time -- on the ID to appplicantID -- and you have your answer:


SELECT A.*
FROM 
 APPLICATIONS A
INNER JOIN
 (the last SQL statement, above) B
ON
  A.Application_ID = B.ID

Does this make sense/help at all? Work it out, step by step and it will make sense. Always approach these problems in SQL from the "inside out".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources