Returning only 1 row that meets some criteria

Question

This is an issue that I run into quite frequently, and which I would love to know how to work through.

I'm writing a report/query that is simple enough, but when run, returns rows with duplicate values. I know the reason these "duplicates" are returned is because there is a column that has differing data between the two rows, but say I only want to see 1 row returned with a specific ID number. It doesn't matter which row is selected, just that only 1 row is returned for each ID_NUM.

Simplified example of returned data:

ID_NUM  |  PHASE  |  DATE  |  NOTE  
----------------------------------
30329   |  Phase1  | 1-1-20 | example note
30329   |  null    | 1-1-20 | example note
21928   |  Phase1  | 1-2-20 | another note
21928   |  Phase1  | 4-3-19 | another note

What I have tried:

SELECT DISTINCT - returns same result as simple SELECT
SELECT DISTINCT MIN(ID_NUM) - still returns duplicate ID_NUM values, I'm guessing because they are the same, there is no min.
GROUP BY ID_NUM - requires that I include every column in the group by, and still returns same result.

Actual Example: While the above is a simplified example, the below shows my actual query. cases.casenum is the field/column that I need only 1 row returned for each unique value. This is a sybase database, which follows mssql/t-sql syntax.

SELECT cases.casenum,
cases.case_date_5, 
cases.class, 
user_tab6_data.trial_phase, 
user_tab6_data.maximizer_completed, 
user_tab6_data.maximizer_scheduled, 
cases.case_title, 
cases.open_status, cases.case_date_9, 
user_tab6_data.maximizer_necessary, 
cases.staff_1, 
cases.staff_8, 
user_tab6_data.county_of_suit, 
insurance.how_settled, 
insurance.policy_type, 
insurance.date_settled, 
user_tab6_data.value_set_date, 
user_tab6_data.status_note, 
user_tab6_data.minimum_value 
FROM cases, user_tab6_data, insurance 
WHERE (cases.casenum = user_tab6_data.case_id) 
AND (cases.casenum = insurance.case_num) 
AND (cases.class like 'A' OR cases.class like 'B' OR cases.class like 'C' OR cases.class like 'D' OR cases.class like 'E' OR cases.class like 'F' OR cases.class like 'G' OR cases.class like 'H' OR cases.class like 'I' OR user_tab6_data.trial_phase like 'Phase 1' OR cases.case_date_5 is not NULL ) 
AND insurance.policy_type = 'Liability' 
AND cases.open_status = 'O' 
AND cases.case_date_9 is not NULL

score 1 · Accepted Answer · answered Apr 30 '20 at 09:42

Try using ROW_NUMBER windowing function as in this example:

--demo setup
drop table if exists Table1
go
CREATE TABLE Table1 (
  ID_NUM INTEGER,
  PHASE VARCHAR(6),
  DATE DATE,
  NOTE VARCHAR(12)
);

--solution
INSERT INTO Table1
  (ID_NUM, PHASE, DATE, NOTE)
VALUES
  ('30329', 'Phase1', '1-1-20', 'example note'),
  ('30329', 'null', '1-1-20', 'example note'),
  ('21928', 'Phase1', '1-2-20', 'another note'),
  ('21928', 'Phase1', '4-3-19', 'another note');

select * from
(
select *,ROW_NUMBER() over(partition by ID_NUM order by ID_NUM) as rn from Table1
) a
where rn = 1

| ID_NUM | PHASE  | DATE       | NOTE         | rn |
|--------|--------|------------|--------------|----|
| 21928  | Phase1 | 2020-01-02 | another note | 1  |
| 30329  | Phase1 | 2020-01-01 | example note | 1  |

Returning only 1 row that meets some criteria

1 Answers1