Home » SQL & PL/SQL » SQL & PL/SQL » Query: select id where max(value) - possible? (Oracle)
Query: select id where max(value) - possible? [message #653571] |
Mon, 11 July 2016 14:07 |
|
ich01
Messages: 3 Registered: July 2016
|
Junior Member |
|
|
Hi @all
at the beginning i thought it is a easy query, because in my opinion it is a typcial query. But i could find a correct query So maybe one of you can solve this problem.
given table Tab1:
id, titel, major, minor
1, abc, 1,1
2, abc, 1,2
3, abc, 2,1
4, abc, 2,3
5, xyz, 1,1
6, xyz, 3,2
7, xyz, 2,1
8, abc, 2,2
This result i expect
4, abc
6, xyc
A query like this or with having doesn't work:-(
SELECT id, titel
FROM Tab1
WHERE max(concat(major,minor)) as version
Group by id, titel
Any notes for me?
Thanks a lot, cheers
ich
|
|
|
Re: Query: select id where max(value) - possible? [message #653575 is a reply to message #653571] |
Mon, 11 July 2016 14:23 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
ich01 wrote on Mon, 11 July 2016 12:07Hi @all
at the beginning i thought it is a easy query, because in my opinion it is a typcial query. But i could find a correct query So maybe one of you can solve this problem.
given table Tab1:
id, titel, major, minor
1, abc, 1,1
2, abc, 1,2
3, abc, 2,1
4, abc, 2,3
5, xyz, 1,1
6, xyz, 3,2
7, xyz, 2,1
8, abc, 2,2
This result i expect
4, abc
6, xyc
Welcome to the forum.
Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
form where does "xyc" originate?
|
|
|
|
Re: Query: select id where max(value) - possible? [message #653577 is a reply to message #653571] |
Mon, 11 July 2016 14:30 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This clause,WHERE max(concat(major,minor)) as version is syntactically incorrect. First, a predicate compares two values; your predicate has only one value and no comparison operator. Second, the keyword AS is used to name a projected column; it has no place in a predicate. Does that give you enough hints to try again?
|
|
|
|
Re: Query: select id where max(value) - possible? [message #653579 is a reply to message #653577] |
Mon, 11 July 2016 14:42 |
|
ich01
Messages: 3 Registered: July 2016
|
Junior Member |
|
|
@blackswan thx for your not. i wrote in my expected result a wrong letter (xyc instead of xyz)
id, titel, major, minor
1, abc, 1,1
2, abc, 1,2
3, abc, 2,1
4, abc, 2,3
5, xyz, 1,1
6, xyz, 3,2
7, xyz, 2,1
8, abc, 2,2
This result i expect
4, abc
6, xyz
@ThomasG thx for the hint with the inline view...i have to try this tomorrow. This could work .
@John Watson thx for your tip. I know it was not the syntactically right query this statement was only for example what kind of query i would expect for my requested result. It tried today many querys, but it didn't work any of them.
[Updated on: Mon, 11 July 2016 14:45] Report message to a moderator
|
|
|
|
|
|
Re: Query: select id where max(value) - possible? [message #653583 is a reply to message #653582] |
Mon, 11 July 2016 14:51 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Mon, 11 July 2016 21:44Michel Cadot wrote on Mon, 11 July 2016 21:38
In addition to BlackSwan's post, always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
|
|
|
Re: Query: select id where max(value) - possible? [message #653590 is a reply to message #653571] |
Mon, 11 July 2016 17:52 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
-- test data:
SCOTT@orcl_12.1.0.2.0> select * from tab1
2 /
ID TITEL MAJOR MINOR
---------- ----- ---------- ----------
1 abc 1 1
2 abc 1 2
3 abc 2 1
4 abc 2 3
5 xyz 1 1
6 xyz 3 2
7 xyz 2 1
8 abc 2 2
8 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> select id, titel, major || '.' || minor version
2 from (select id, titel, major, minor,
3 dense_rank () over
4 (partition by titel
5 order by major desc, minor desc) dr
6 from tab1 a)
7 where dr = 1
8 /
ID TITEL VERSION
---------- ----- -------
4 abc 2.3
6 xyz 3.2
2 rows selected.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:35:10 CDT 2024
|