Home » SQL & PL/SQL » SQL & PL/SQL » group by with tricks (10g)
group by with tricks [message #361455] Wed, 26 November 2008 08:55 Go to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
I need to modify SQL. But when I start checking it I found that group by statement unusual.
1- Order in select is not the same as in group by
2- some fields in select not inside group by
3- some fields only one time in group by and few time in select

SQL is working and result is correct

my question is --> Is it legal
thanks


select
(
select count(*)
from spriden en
where
en.spriden_pidm = spriden_pidm


),
spriden_pidm,
count(*) ,
substr(spriden_last_name,1,2),
spriden_last_name

from
spriden
group by spriden_pidm ,spriden_last_name

[Updated on: Wed, 26 November 2008 08:57]

Report message to a moderator

Re: group by with tricks [message #361457 is a reply to message #361455] Wed, 26 November 2008 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

1- Why not?
2- Why not?
3- ? (maybe it is clear when query is formatted but there I don't understand the point)

Regards
Michel


Re: group by with tricks [message #361460 is a reply to message #361457] Wed, 26 November 2008 09:12 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
Michel Thank you for your answer


3)
third question was

the same table field two times in select

I mean

select a-field , substr( a-field ,2,5), count(*)
......
......
group by a-field
order by....

I am new in oracle and I am not sure it is correct
Thanks

Re: group by with tricks [message #361461 is a reply to message #361455] Wed, 26 November 2008 09:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

3. It wont work .

Smile
Rajuvan.
Re: group by with tricks [message #361468 is a reply to message #361461] Wed, 26 November 2008 09:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
3. It wont work .

Bets?
SQL> with src as (select 'Row 1' col_1 from dual union all
  2               select 'Row 1' col_1 from dual union all
  3               select 'Row 2' col_1 from dual union all
  4               select 'Row 2' col_1 from dual union all
  5               select 'Row 3' col_1 from dual union all
  6               select 'Row 3' col_1 from dual )
  7  select col_1,substr(col_1,1,3),count(*) 
  8  from   src
  9  group by col_1;

COL_1 SUB   COUNT(*)
----- --- ----------
Row 1 Row          2
Row 2 Row          2
Row 3 Row          2
Re: group by with tricks [message #361469 is a reply to message #361455] Wed, 26 November 2008 09:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
SQL is working and result is correct

my question is --> Is it legal


Yes, by definition if it works and doesn't give an error then it is a piece of legal SQL.

There are a few rare ocasions when pieces of SQL work when they shouldn't (or don't work when they should) but as a rule of thumb, working trumps legal.
Re: group by with tricks [message #361473 is a reply to message #361469] Wed, 26 November 2008 09:46 Go to previous messageGo to next message
ykozhevnikov
Messages: 58
Registered: November 2008
Location: USA
Member
Thanks Smile
Re: group by with tricks [message #361481 is a reply to message #361455] Wed, 26 November 2008 10:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@JRowBottom,

Will bet next time .
I am so silly sometimes.

I didn't notice it properly .

./fa/1587/0/
( Second hammer today !!! )


Sad
Rajuvan.
Re: group by with tricks [message #361486 is a reply to message #361481] Wed, 26 November 2008 10:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A lesson I learned here some years ago was that if I posted that sometiong couldn't be done, then almost invariably someone else would post an example of how to do it.

It was a humbling, but very educational experience.
Re: group by with tricks [message #361488 is a reply to message #361455] Wed, 26 November 2008 11:22 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even I have the same experience here.I started learning good oracle techniques from Orafaq only. No book can match that experience.

Its purely unique Orafaq experience for me Smile

Rajuvan.
Previous Topic: hadling set serverout put on on package/procedures
Next Topic: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb)
Goto Forum:
  


Current Time: Sat Dec 10 18:14:21 CST 2016

Total time taken to generate the page: 0.06872 seconds