Home » SQL & PL/SQL » SQL & PL/SQL » Query help (12.2)
Query help [message #668405] Thu, 22 February 2018 22:00 Go to next message
kapilavastu
Messages: 9
Registered: July 2015
Junior Member
I have a data set like this.

col1  col2   col3  col4

A      B      C     10DIGIT
A      C      D     10DIGIT
B      A      D     10DIGIT
B      A      D      6DIGIT
E      A      G      6DIGIT

I would like the result set to be

col1   col2     col3     col4

A        B       C      10DIGIT
A        C       D      10DIGIT
B        A       D      10DIGIT
E        A       G      6DIGIT

Basically if there is a group of values that exists at 10 digit as well as 6digit ( col 4), the result set should include only 10digit rows and any other rows that are not duplicate at 10 digit level but are at 6 digit level need to be included.
Re: Query help [message #668406 is a reply to message #668405] Fri, 23 February 2018 01:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far? The CREATE TABLE and INSERT statements would be a good start.
Re: Query help [message #668407 is a reply to message #668406] Fri, 23 February 2018 02:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
MAX(col4) (with other columns in the GROUP BY clause) might do the job; at least, according to what you've said so far.
Re: Query help [message #668411 is a reply to message #668407] Fri, 23 February 2018 03:48 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Please use below query:

WITH TAB AS (
SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '10DIGIT' COL4 FROM DUAL UNION
SELECT 'A',      'C',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',      '6DIGIT' FROM DUAL UNION
SELECT 'E',      'A',     'G',      '6DIGIT' FROM DUAL )
SELECT COL1,COL2,COL3,MAX(COL4) FROM TAB GROUP BY COL1,COL2,COL3;
Re: Query help [message #668418 is a reply to message #668411] Fri, 23 February 2018 07:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
anil_mk wrote on Fri, 23 February 2018 01:48
Please use below query:

WITH TAB AS (
SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '10DIGIT' COL4 FROM DUAL UNION
SELECT 'A',      'C',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',      '6DIGIT' FROM DUAL UNION
SELECT 'E',      'A',     'G',      '6DIGIT' FROM DUAL )
SELECT COL1,COL2,COL3,MAX(COL4) FROM TAB GROUP BY COL1,COL2,COL3;
I don't think so. Do you know why not?
SQL> WITH TAB AS (
SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '10DIGIT' COL4 FROM DUAL UNION
SELECT 'A',      'C',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',     '10DIGIT' FROM DUAL UNION
SELECT 'B',      'A',     'D',      '6DIGIT' FROM DUAL UNION
SELECT 'E',      'A',     'G',      '6DIGIT' FROM DUAL )
SELECT COL1,COL2,COL3,MAX(COL4) FROM TAB GROUP BY COL1,COL2,COL3;  2    3    4    5    6    7  

C C C MAX(COL
- - - -------
A C D 10DIGIT
E A G 6DIGIT
A B C 10DIGIT
B A D 6DIGIT
"6" is greater than "1"
Re: Query help [message #668419 is a reply to message #668411] Fri, 23 February 2018 07:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
anil_mk wrote on Fri, 23 February 2018 04:48
Please use below query:
Please test your code before suggesting. Strings are compared left to right character by character, so '6DIGIT' is greater than '10DIGIT':

SQL> WITH TAB AS (
  2  SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '10DIGIT' COL4 FROM DUAL UNION
  3  SELECT 'A',      'C',     'D',     '10DIGIT' FROM DUAL UNION
  4  SELECT 'B',      'A',     'D',     '10DIGIT' FROM DUAL UNION
  5  SELECT 'B',      'A',     'D',      '6DIGIT' FROM DUAL UNION
  6  SELECT 'E',      'A',     'G',      '6DIGIT' FROM DUAL )
  7  SELECT COL1,COL2,COL3,MAX(COL4) FROM TAB GROUP BY COL1,COL2,COL3;

C C C MAX(COL
- - - -------
A C D 10DIGIT
E A G 6DIGIT
A B C 10DIGIT
B A D 6DIGIT <-- should be '10DIGIT'

SQL> 

Assuming COL4 is always followed by 5 character word DIGIT and doesn't have leading zeroes:

WITH TAB AS (
             SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '10DIGIT' COL4 FROM DUAL UNION
             SELECT 'A',      'C',     'D',     '10DIGIT' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '10DIGIT' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '6DIGIT' FROM DUAL UNION
             SELECT 'E',      'A',     'G',      '6DIGIT' FROM DUAL
            )
SELECT  COL1,
        COL2,
        COL3,
        LTRIM(MAX(LPAD(COL4,100,0)),0) COL4
  FROM  TAB
  GROUP BY COL1,
           COL2,
           COL3
/

COL1 COL2 COL3 COL4
---- ---- ---- ----------
A    C    D    10DIGIT
E    A    G    6DIGIT
A    B    C    10DIGIT
B    A    D    10DIGIT

SQL> 

SY.
Re: Query help [message #668420 is a reply to message #668418] Fri, 23 February 2018 07:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I understood it as

10DIGIT = 1234567890 or 
          9865254775 or
          2256897774

6DIGIT  = 236598     or
          879822

i.e. not literally.

MAX, in that case, does the job.
Re: Query help [message #668421 is a reply to message #668420] Fri, 23 February 2018 07:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Littlefoot wrote on Fri, 23 February 2018 08:18

MAX, in that case, does the job.
No, it does not. Again,strings are compared left to right character by character, so: '879822' is greater than '1234567890'

SY.

Re: Query help [message #668422 is a reply to message #668421] Fri, 23 February 2018 07:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You would need to do MAX(TO_NUMBER(COL4)) but that again assumes COL4 has no leading zeroes.

SY.
Re: Query help [message #668423 is a reply to message #668422] Fri, 23 February 2018 07:57 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Solomon Yakobson wrote on Fri, 23 February 2018 08:53
You would need to do MAX(TO_NUMBER(COL4)) but that again assumes COL4 has no leading zeroes.

SY.
I think Littlefoot was saying that if it were actually 10 digit numbers and 6 digit numbers, the MAX would work. I "think" he was under the impression that OP was lazy and didn't want to post 6 digit and 10 digit numbers and just put the string 6digit and 10digit to save time. I don't think Littlefoot would make a mistake like that.
Re: Query help [message #668425 is a reply to message #668422] Fri, 23 February 2018 08:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
After re-reading original post I think OP wants to select all rows for each combination of COL1, COL2, COL3 where number of digits is the highest. If so:

WITH TAB AS (
             SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '3456789012' COL4 FROM DUAL UNION
             SELECT 'A',      'C',     'D',     '1234567890' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '0001234567' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '9876543210' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '4567890123' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '1324658709' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '987654' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '654321' FROM DUAL UNION
             SELECT 'E',      'A',     'G',      '123654' FROM DUAL
            ),
       T AS (
             SELECT  TAB.*,
                     CASE MAX(LENGTH(COL4)) OVER(PARTITION BY COL1,COL2,COL3)
                       WHEN LENGTH(COL4) THEN 1
                     END FLAG
               FROM  TAB
            )
SELECT  COL1,
        COL2,
        COL3,
        COL4
  FROM  T
  WHERE FLAG = 1
  ORDER BY COL1,
           COL2,
           COL3,
           COL4
/

COL1 COL2 COL3 COL4
---- ---- ---- ----------
A    B    C    3456789012
A    C    D    1234567890
B    A    D    0001234567
B    A    D    1324658709
B    A    D    4567890123
B    A    D    9876543210
E    A    G    123654

7 rows selected.

SQL> 

And if COL4 can have leading zeroes and we we don't want to consider them then:

WITH TAB AS (
             SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '3456789012' COL4 FROM DUAL UNION
             SELECT 'A',      'C',     'D',     '1234567890' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '0001234567' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '9876543210' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '4567890123' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '1324658709' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '987654' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '654321' FROM DUAL UNION
             SELECT 'E',      'A',     'G',      '123654' FROM DUAL
            ),
       T AS (
             SELECT  TAB.*,
                     CASE MAX(LENGTH(TO_NUMBER(COL4))) OVER(PARTITION BY COL1,COL2,COL3)
                       WHEN LENGTH(TO_NUMBER(COL4)) THEN 1
                     END FLAG
               FROM  TAB
            )
SELECT  COL1,
        COL2,
        COL3,
        COL4
  FROM  T
  WHERE FLAG = 1
  ORDER BY COL1,
           COL2,
           COL3,
           COL4
/

COL1 COL2 COL3 COL4
---- ---- ---- ----------
A    B    C    3456789012
A    C    D    1234567890
B    A    D    1324658709
B    A    D    4567890123
B    A    D    9876543210
E    A    G    123654

6 rows selected.

SQL> 

SY.
Re: Query help [message #668426 is a reply to message #668423] Fri, 23 February 2018 08:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
joy_division wrote on Fri, 23 February 2018 08:57

I think Littlefoot was saying that if it were actually 10 digit numbers and 6 digit numbers, the MAX would work. I "think" he was under the impression that OP was lazy and didn't want to post 6 digit and 10 digit numbers and just put the string 6digit and 10digit to save time. I don't think Littlefoot would make a mistake like that.
WITH TAB AS (
             SELECT 'A' COL1,'B' COL2,'C' COL3,'3456789012' COL4 FROM DUAL UNION
             SELECT 'A',     'B',     'C',     '987654' FROM DUAL
            )
SELECT  COL1,
        COL2,
        COL3,
        MAX(COL4) COL4_LF,
        MAX(TO_NUMBER(COL4)) COL4_SY
  FROM  TAB
  GROUP BY COL1,
           COL2,
           COL3
/

COL1 COL2 COL3 COL4_LF        COL4_SY
---- ---- ---- ---------- -----------
A    B    C    987654      3456789012

SQL> 

SY.
Re: Query help [message #668427 is a reply to message #668425] Fri, 23 February 2018 08:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, I overcomplicated it. No need for CASE + MAX, plain DENSE_RANK would do:

WITH TAB AS (
             SELECT 'A' COL1,      'B' COL2,     'C' COL3,     '3456789012' COL4 FROM DUAL UNION
             SELECT 'A',      'C',     'D',     '1234567890' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '0001234567' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '9876543210' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '4567890123' FROM DUAL UNION
             SELECT 'B',      'A',     'D',     '1324658709' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '987654' FROM DUAL UNION
             SELECT 'B',      'A',     'D',      '654321' FROM DUAL UNION
             SELECT 'E',      'A',     'G',      '123654' FROM DUAL
            ),
       T AS (
             SELECT  TAB.*,
                     DENSE_RANK() OVER(PARTITION BY COL1,COL2,COL3 ORDER BY LENGTH(COL4) DESC) FLAG
               FROM  TAB
            )
SELECT  COL1,
        COL2,
        COL3,
        COL4
  FROM  T
  WHERE FLAG = 1
  ORDER BY COL1,
           COL2,
           COL3,
           COL4
/

COL1 COL2 COL3 COL4
---- ---- ---- ----------
A    B    C    3456789012
A    C    D    1234567890
B    A    D    0001234567
B    A    D    1324658709
B    A    D    4567890123
B    A    D    9876543210
E    A    G    123654

7 rows selected.

SQL> 

SY.
Re: Query help [message #668428 is a reply to message #668427] Fri, 23 February 2018 08:22 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
and another approach (in case you only want one "col4" per grouping set):
WITH
    TAB AS
        (SELECT 'A' COL1, 'B' COL2, 'C' COL3, '10DIGIT' COL4 FROM DUAL
         UNION
         SELECT 'A', 'C', 'D', '10DIGIT' FROM DUAL
         UNION
         SELECT 'B', 'A', 'D', '10DIGIT' FROM DUAL
         UNION
         SELECT 'B', 'A', 'D', '6DIGIT' FROM DUAL
         UNION
         SELECT 'E', 'A', 'G', '6DIGIT' FROM DUAL)
SELECT   DISTINCT
         COL1
        ,COL2
        ,COL3
        ,FIRST_VALUE(COL4) OVER(PARTITION BY COL1, COL2, COL3 ORDER BY LENGTH(COL4) DESC, COL4 DESC) AS COL4
    FROM TAB
ORDER BY 1, 2, 3

[Updated on: Fri, 23 February 2018 08:34]

Report message to a moderator

Re: Query help [message #668429 is a reply to message #668426] Fri, 23 February 2018 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Fri, 23 February 2018 14:10
joy_division wrote on Fri, 23 February 2018 08:57

I think Littlefoot was saying that if it were actually 10 digit numbers and 6 digit numbers, the MAX would work. I "think" he was under the impression that OP was lazy and didn't want to post 6 digit and 10 digit numbers and just put the string 6digit and 10digit to save time. I don't think Littlefoot would make a mistake like that.
......

SY.
Sy - 6DIGIT and 10DIGIT are obviously strings.
But, if they're just short hands to indicate the state of the underlying data then there's no reason to assume col4 isn't actually a number.
It's not like the OP gave a create table / specified data types.
And in that case LF's solution will work.

Of course it might be that the actual data is what the OP originally posted, we're all just guessing at this point.
Re: Query help [message #668431 is a reply to message #668429] Fri, 23 February 2018 11:51 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I was talking about numbers.

10DIGIT = 1234567890    --> a number, as opposed to
10DIGIT = '1234567890'  --> a string
Previous Topic: User variable to decide which in statement to use in query
Next Topic: Character set Conversion
Goto Forum:
  


Current Time: Thu Mar 28 08:15:39 CDT 2024