0

I have a table as below.

OId CustId CustSeq
1   A      10
1   A      20
2   A      10
2   A      20

I'm trying to extract unique records as below.

OId CustId CustSeq (Different OIds with different CustSeqs)
1   A      10
2   A      20

May I know how I could come out the query to extract like above?

Alexandru Chichinete
  • 1,166
  • 12
  • 23
TNA
  • 616
  • 1
  • 9
  • 25
  • Look here: https://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj32654.html – bdn02 Jan 20 '16 at 07:45
  • This seems to be similar to this one: http://stackoverflow.com/q/34874749/330315 –  Jan 20 '16 at 08:03
  • @bdn02: that is **not** the "Oracle manual". It's the manual for the embedded JavaDB - which is something completely different than the Oracle database –  Jan 20 '16 at 08:03
  • In my mind raises this question: "How do appear these inputs? Something is wrong elsewhere". However, nice problem. – Florin Ghita Jan 20 '16 at 08:09
  • @Florin Ghita , messed up data of course,but no choice have to deal with it :D – TNA Jan 20 '16 at 08:19
  • If that is the exact output expected i.e. only 2 rows then how are you deciding for OId 1 and CustId A to show a CustSeq of 10 and not 20? – Alan Macdonald Jan 20 '16 at 08:58
  • If Oid 1 with CustId A shows CustSeq of 20, I want Oid 2 with CustId to show CustSeq of 10, it's ok as long as seq are not repeated. – TNA Jan 20 '16 at 09:23
  • I just don't want the results to be sth like Oid 1 with CustId A shows the same CustSeq as Oid 2 with CustId A (i.e both are showing CustSeq 10 or 20). – TNA Jan 20 '16 at 09:25

2 Answers2

0

Just use DISTINCT. That's what it was desgined for although group by will work.

http://www.techonthenet.com/oracle/distinct.php

SELECT DISTINCT OID, CUSTID, CUSTSEQ
FROM TABLE_NAME
Alan Macdonald
  • 1,872
  • 20
  • 36
  • 2
    `1 A 10` is different from `2 A 10` meaning the distinct won't do anything – Alexandru Chichinete Jan 20 '16 at 07:56
  • add to the query `group by CustId, CustSeq` – Mahmoud Tantawy Jan 20 '16 at 08:07
  • Depends whether the "extract unique records" in the quesiton is wrong or if the given output is exhaustive for the example. The question doesn't explain if the final column isn't part of the uniqueness then which value is to be selected i.e. 10 or 20 or if it matters – Alan Macdonald Jan 20 '16 at 08:57
  • That's what I've said. `1 A 10`, `2 A 10`, `1 A 20`, `2 A 20` are all DISTINCT – Alexandru Chichinete Jan 20 '16 at 09:01
  • @AlexandruChichinete yeah sorry I don't find the question clear on how to select the final column value when the first two columns are duplicated – Alan Macdonald Jan 20 '16 at 09:06
  • I want to extract unique OID with different CustSeq, eg. If Oid 1 already has CustSeq 10, for Oid 2, CustSeq should be 20. – TNA Jan 20 '16 at 09:12
  • @TNA sorry that's not clear. So because any Oid (1 in this case) has already had a CustSeq of 10 then then on the next Oid (2 in this case( it should pick the next highest number after 10 (20 in this case)? I would strongly recommending updating your question with details on your logic for picking this final value. – Alan Macdonald Jan 20 '16 at 09:23
  • If Oid has already had a CustSeq of 10, next Oid 2 should pick up the seq different than Oid 1 (can be lower or higher as long as it's not 10). I've updated in comments of main question as well. – TNA Jan 20 '16 at 09:29
  • 1
    @TNA to do this purely in SQL and not involve code will be pretty challenging and take a lot of time. I'm at work at the moment so won't be able to look for a while but if you start reading about the Rank function I think you'd need to rank the CustSeqs and then join to another query for the actual row data in some way but it will be really tricky. Is achivieving this in code instead an option? https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm – Alan Macdonald Jan 20 '16 at 10:37
  • @Alan Macdonald, yes, it's too troublesome to handle this logic in sql, I've just moved this logic to code, it's much more simpler and easier to maintain. Thanks. – TNA Jan 21 '16 at 03:12
0

Use DISTINCT, and also use Group By for the 2 columns CustId & CustSeq

Check here for example Is it possible to GROUP BY multiple columns using MySQL?

Community
  • 1
  • 1
Mahmoud Tantawy
  • 713
  • 5
  • 12
  • I want not only unique OIds but also unique CustSeqs, only distinct and group by won't solve the problem for sure – TNA Jan 20 '16 at 08:29
  • You can use more than one column in `Group By` , Why do you think this won't solve your problem? – Mahmoud Tantawy Jan 20 '16 at 08:30
  • correct, but, in my case , if oid and custseq are grouped by, it will still output result as 4 records. – TNA Jan 20 '16 at 08:34