CODEWITHSUNDEEP
sqlt-sql
Michael Gee
Michael Gee

Reputation: 11

Eliminate Multiple Records In Query

I have a select I want only one record returned for each case and am having a problem with it.

Example:

Select 
    CaseId, Party_id, RANKING
from...

The problem is at the charge level the case can have similar multiple charges...

Charges

    Case   ChargeCount  RANKING    
     1           1        800
     2           1        802
     2           2        803 
     3           1        800

I only want 3 cases returned with the first charge meeting the criteria selected. I tried using a simple partition by over caseId but this messed up the counts elsewhere. Is there other ways to do this???

Thanks

Upvotes: 0

Views: 69

Answers (2)

gcbenison
gcbenison

Reputation: 11963

Assuming you want exactly one row returned per CaseID:

Select 
    CaseId, Party_id, RANKING
from...
GROUP BY CaseID

Note that where there are multiple possible answers for each row, this will return an arbitrary one unless you define somehow the one to pick.

Upvotes: 0

Tejs
Tejs

Reputation: 41256

How about:

SELECT CaseId, ChargeCount, Ranking FROM SomeTable WHERE ChargeCount = 1

Unless I'm missing something, it's that simple. Your example query is not exactly very illuminating to the underlying structures that you have presented.

Upvotes: 1

Related Questions