OraFAQ Forum: SQL & PL/SQL » Query: select id where max(value)

Home » SQL & PL/SQL » SQL & PL/SQL » Query: select id where max(value) - possible? (Oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 Surprised

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

Report message to a moderator

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:07

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

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?

Report message to a moderator

Re: Query: select id where max(value) - possible? [message #653576 is a reply to message #653571]

Mon, 11 July 2016 14:27

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

You could probably either use a Sub-Query, or a Inline View with "analytical rank".

Report message to a moderator

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: 8931
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?

Report message to a moderator

Re: Query: select id where max(value) - possible? [message #653578 is a reply to message #653571]

Mon, 11 July 2016 14:38

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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.

Report message to a moderator

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 Smile

.

@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 #653580 is a reply to message #653579]

Mon, 11 July 2016 14:44

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Quote:

It tried today many querys, but it didn't work any of them.

What were they? Did they fail syntactically, or did they run but give the wrong result?

Report message to a moderator

Re: Query: select id where max(value) - possible? [message #653581 is a reply to message #653580]

Mon, 11 July 2016 14:44

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel 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.

Report message to a moderator

Re: Query: select id where max(value) - possible? [message #653582 is a reply to message #653581]

Mon, 11 July 2016 14:49

ich01
Messages: 3
Registered: July 2016

Junior Member

@John Watson both one time syntactically and otherwise a wrong result --> tomorrow i will try this inline view (answer from ThomasG)

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: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 11 July 2016 21:44

Michel 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.

Report message to a moderator

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: 9090
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.

Report message to a moderator

Previous Topic:	information
Next Topic:	finding constrains on table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:29:40 CDT 2024