Home » SQL & PL/SQL » SQL & PL/SQL » Query help (12.2)
Query help [message #668405] |
Thu, 22 February 2018 22:00 |
|
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 #668411 is a reply to message #668407] |
Fri, 23 February 2018 03:48 |
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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
anil_mk wrote on Fri, 23 February 2018 01:48Please 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 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
anil_mk wrote on Fri, 23 February 2018 04:48Please 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 #668421 is a reply to message #668420] |
Fri, 23 February 2018 07:52 |
Solomon Yakobson
Messages: 3275 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 #668423 is a reply to message #668422] |
Fri, 23 February 2018 07:57 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Solomon Yakobson wrote on Fri, 23 February 2018 08:53You 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 |
Solomon Yakobson
Messages: 3275 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 |
Solomon Yakobson
Messages: 3275 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 |
Solomon Yakobson
Messages: 3275 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 |
|
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 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Solomon Yakobson wrote on Fri, 23 February 2018 14:10joy_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 |
|
Littlefoot
Messages: 21811 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
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:14:49 CDT 2024
|