Home » SQL & PL/SQL » SQL & PL/SQL » group by query
icon5.gif  group by query [message #222383] Sat, 03 March 2007 13:38 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
I have 3 records in BUYER table:-

ID NAME STATE COUNTRY CODE
01 AA A1 A4 A01
01 AA A2 A3 A02
02 BB B1 B2 B01

1) Considering 'Code' is unique.
2) ID might have more than one same.


I was using 'select max(ID), max(NAME), max(STATE), max(COUNTRY), max(CODE) from buyer group by ID'.

What i get was :-
01 AA A2 A4 A02 -- 1st record
02 BB B1 B2 B01 -- 2nd record

U notice that 1st record the data was mix up between the two same ID records.

How can i get :-

01 AA A1 A4 A01 -- 1st record
02 BB B1 B2 B01 -- 2nd record

The problem was caused by max() function. And i cant ignore the max() function because they required an aggregate funtion when i used group by.

Any help?
Re: group by query [message #222390 is a reply to message #222383] Sat, 03 March 2007 18:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could do it with analytic functions.

select id, name, state, country, code
from (
    select a.*
    ,      row_number() over (partition by id order by code) as rn
    from   buyer
)
where rn = 1


Ross Leishman
Re: group by query [message #222490 is a reply to message #222390] Mon, 05 March 2007 02:57 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from emp where rowid in (select max(rowid) from emp union select min(rowid) from emp )
Re: group by query [message #222517 is a reply to message #222490] Mon, 05 March 2007 04:58 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from (select FIRST_VALUE(name) over(partition by id order by name) as name,
FIRST_VALUE(id) over(partition by deptno order by name) as id, .........
from emp) group by id,name,...........
Re: group by query [message #222518 is a reply to message #222490] Mon, 05 March 2007 05:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select * from emp where rowid in (select max(rowid) from emp union select min(rowid) from emp ) 


What exactly is this query intended to achieve?
In the very specific case where the two rows that you want happen to have the Max and Min rowids , then it will return the correct results - in every other case, it will fail.
If you want to get data for THREE codes - it will fail.
If you change the order of the insert statements - it will fail.
If you add another row (and I really do think that this question was a simplification of a more complex situation) - it will fail.

Do you honestly think that this answer was worth the time it took to type in?

{typos}

[Updated on: Mon, 05 March 2007 05:01]

Report message to a moderator

Previous Topic: select rows with stored procedure
Next Topic: Query needed
Goto Forum:
  


Current Time: Fri Dec 02 16:24:37 CST 2016

Total time taken to generate the page: 0.26113 seconds