Home » SQL & PL/SQL » SQL & PL/SQL » the first of each group
the first of each group [message #205399] Fri, 24 November 2006 08:58 Go to next message
iñigo
Messages: 20
Registered: January 2003
Junior Member
hello

I have a query

select count(*) c1 ,c2,c3 from table1
group by c2, c3
order by c2, c1 desc

That returns this result
C1 C2 C3
------------------------------------
90913 1 A
5907 1 B
5207 1 C
4529 1 D
1054 1 E
18489 2 B
14089 2 H
13404 2 A
5278 2 B
10796 3 C
10555 3 D
7537 3 I
2023 3 A
7430 4 I
6421 4 D
4966 4 H
1839 4 A
1551 4 B
5172 5 D
4560 5 I
3681 5 H
1502 5 B
1078 5 A
1018 5 C

Is there a simply method to obtain de first record of each C2 field

I want this
C1 C2 C3
------------------------------------
90913 1 A
18489 2 B
10796 3 C
7430 4 I
5172 5 D

Thanks
Re: the first of each group [message #205440 is a reply to message #205399] Fri, 24 November 2006 16:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Analytic Function time.

(untested code, cos I can't be bothered to start up a database)
SELECT distinct
       last_value(c1) over (partition by c2 order by c1) c1
      ,c2
      ,last_value(c3) over (partition by c2 order by c1) c1
FROM(SELECT count(*) c1 ,c2,c3 
     FROM   table1
     GROUP BY c2, c3)
Re: the first of each group [message #205718 is a reply to message #205440] Mon, 27 November 2006 04:19 Go to previous messageGo to next message
iñigo
Messages: 20
Registered: January 2003
Junior Member
Thanks for your answer JRowbottom, but the result isn't the correct.
Re: the first of each group [message #205719 is a reply to message #205718] Mon, 27 November 2006 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hang on a second - I'll get my psychic helmet and see if I can read your mind and extract the details of the problem directly, without having to trouble you to write then down.....

Drat - it's in the repair shop.

I guess you'll just have to tell us what the problem is yourself.

Re: the first of each group [message #205721 is a reply to message #205719] Mon, 27 November 2006 04:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've worked out what the problem is, but I'm still going to make you provide a more useful reply before I give you the corrected version.
Re: the first of each group [message #205726 is a reply to message #205718] Mon, 27 November 2006 04:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I read your problem as the following.
You have this
C1    C2 C3
90913 1 A
5907  1 B
5207  1 C
4529  1 D
1054  1 E
And you want the following
90913 1 A
i.e., first entered row for C2(which is 1)

The same way you want to display "18489 2 B",as it is the first entered rows for C2(which is 2).

It is not possible unless you have a create_date or some column in the table to order by.

By
Vamsi
Re: the first of each group [message #205732 is a reply to message #205726] Mon, 27 November 2006 04:54 Go to previous messageGo to next message
iñigo
Messages: 20
Registered: January 2003
Junior Member
Hi vamsi kasina, it is exactly what I want, the first row (the max value) for each c2 value
Re: the first of each group [message #205733 is a reply to message #205726] Mon, 27 November 2006 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you look at the data this result set is derived from, you'll see that he wants the row equating to the highest value of C1 for each distinct value of C2.

I'll relent and post the solution.
SELECT distinct
       first_value(c1) over (partition by c2 order by c1 desc) c1
      ,c2
      ,first_value(c3) over (partition by c2 order by c1 desc) c3
FROM(first_test)
Re: the first of each group [message #205737 is a reply to message #205733] Mon, 27 November 2006 05:03 Go to previous message
iñigo
Messages: 20
Registered: January 2003
Junior Member
Hi JRowbottom, now it works correctly.
Thanks a lot
Previous Topic: DBMS_SQL
Next Topic: How to find a Nearly Duplicate Record in table
Goto Forum:
  


Current Time: Sat Dec 10 05:17:58 CST 2016

Total time taken to generate the page: 0.26073 seconds