Home » SQL & PL/SQL » SQL & PL/SQL » Top values from a table
Top values from a table [message #2081] Thu, 20 June 2002 03:50 Go to next message
Steve Culshaw
Messages: 3
Registered: June 2002
Junior Member
Can anyone help on this SQL, as I'm just going round in circles?
I've got a table with fields code and list, and I need to get just the first code value for each of the different list values
Re: Top values from a table [message #2083 is a reply to message #2081] Thu, 20 June 2002 05:24 Go to previous messageGo to next message
ram
Messages: 95
Registered: November 2000
Member
select code,list from table a
where code=(select code from table b where
a.code=b.code and
rowid=(select min(rowid) from table c
where b.code=c.code)
group by code)

pl check and inform
Re: Top values from a table [message #2091 is a reply to message #2083] Thu, 20 June 2002 09:30 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
select min(codeva), listname
  from cncode
 group by listname;
Re: Top values from a table [message #2111 is a reply to message #2083] Fri, 21 June 2002 02:11 Go to previous messageGo to next message
Steve Culshaw
Messages: 3
Registered: June 2002
Junior Member
Thanks for the response
- now to be awkward, is there someway to return say 2 or 3 values from the list ?
- reason is I've got to do some documentation, and I need to include examples of each 'list'. I thought one would be enough, but now told need more :-(
Re: Top values from a table [message #2117 is a reply to message #2083] Fri, 21 June 2002 09:39 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Using the analytic functions available in 8i and later, this is easy. Just replace the number in the WHERE clause with the number of values you want to see for each listname (I used 2 in this example):

sql>select listname, codevalue from cncode order by listname, codevalue;
 
L C
- -
1 A
1 B
1 C
2 D
2 E
2 F
3 X
3 Y
3 Z
 
9 rows selected.
 
sql>select listname, codevalue
  2    from (select listname, codevalue, 
  3                 row_number() over 
  4                   (partition by listname order by codevalue) rn
  5            from cncode)
  6   where rn <= 2
  7   order by listname, codevalue;
 
L C
- -
1 A
1 B
2 D
2 E
3 X
3 Y
 
6 rows selected.
Re: Top values from a table [message #2137 is a reply to message #2083] Mon, 24 June 2002 02:29 Go to previous messageGo to next message
Steve Culshaw
Messages: 3
Registered: June 2002
Junior Member
Excellent, that's really helpful
Thanks,
Steve C.
Re: Top values from a table [message #2200 is a reply to message #2083] Thu, 27 June 2002 04:20 Go to previous message
piyush,niranjan
Messages: 1
Registered: June 2002
Junior Member
select min(codevalue),listname
from cncode
group by listname
Previous Topic: User Interaction In PL/SQL
Next Topic: ORACLe-Error
Goto Forum:
  


Current Time: Mon Jul 22 01:24:59 CDT 2019