SQL - How to select only one row for each distinct value

mgrant2012
mgrant2012 used Ask the Experts™
on
I have a pretty long query which includes subqueries and joins that I need to run for a report. It runs without error, but the results show 11 rows for each value, when I need it to display 1 row per value. I'm using 'select distinct', which is what I thought I needed to use to accomplish this goal, but it doesn't seem to be working. What am I doing wrong?

The value that I need to display only 1 row per each distinct occurrence is "cases.casenum".

Here's the query:

SELECT distinct
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS TOTAL_LOST_FILES, cases.casenum AS 'Case Number', user_case_data.discharged_date AS 'Discharged Date',

(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM (cases LEFT JOIN case_checklist ON cases.casenum=case_checklist.case_id)
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
ORDER BY user_case_data.discharged_date ASC;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have to tell more about your data:
1) I suppose the the user_case_data can contain more rows with equal casenum for various discharged_date. Is it correct? If yes then each casenum can be listed more times or you have to specify what discharged_date should appear on output.
2) How many records for one casenum can exist in case_notes?  If the answer is "more than 1" then you have to specify how to handle such situation.
3) How many rows for one case_id can exist if case_checklist?  I would guess o or 1. Correct?
4) Why are you attempting to display "count(distinct cases.casenum)" in each output row? What is the purpose of such value on output?

Author

Commented:
Thank you for the reply!
I've included a screenshot of the output so you can see what I'm talking about, and what I'm after.

To answer your questions:
1) Each case number only has 1 discharge date. So for each row, there should only be one case number, and one discharged date.

2) There can be many records that exist per case number on the case_notes table... hundreds. However, there will only be 1 note with the topic I specified in the case statement (LOS Updated Lein Ltr), and for that one note, I want the date it was posted.

3) Again, there can be many rows that exist for a single case number on the case_checklist table, however I'm only trying to pull the date of 1 single checklist item... specified in the 'case' statement.

4) I don't really want to count the number of distinct cases for each row... there is no purpose for that... I just wanted a count of the total distinct cases somewhere in the output.

Of course, this is really rough and I'm still getting a grip on how to write these long reports... if you have a better way of doing what I'm trying to do, please feel free to share!
Thank you
example.PNG
As visible on the image the Advised Attorney has 2 different dates for one casenum. Following code will display the higher value. The same is also valid for other items on the case_checklist.

The best way how to display distinct casenums is to use GROUP BY and update appropriate values (you may remove the count if it will report error):
SELECT 
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS TOTAL_LOST_FILES, cases.casenum AS 'Case Number', user_case_data.discharged_date AS 'Discharged Date',

MAX(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

MAX(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

MAX(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

MAX(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

MAX(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

MAX(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

MAX(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

MAX(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM cases 
LEFT JOIN case_checklist ON cases.casenum = case_checklist.case_id
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num AND case_notes.topic LIKE 'LOS Case Status Update'
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
GROUP BY cases.casenum, user_case_data.discharged_date
ORDER BY user_case_data.discharged_date ASC;

Open in new window

BTW, as you may see the filter for notes was also added into the JOIN but that's not necessary in this case. And LIKE without any wildcard is not obvious.

Author

Commented:
Amazing! just what I needed. Thanks for that- I'll keep this as a reference going forward. I tried to use GROUP BY before, but it just didn't work... so I'm still not sure what I was missing but thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial