Report query required. [message #337166] |
Wed, 30 July 2008 01:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzad-ul-hasan
Messages: 625 Registered: August 2002
|
Senior Member |
|
|
Problems:
Table Fields:
S-no NewRollno Description Year Pre.RNo Reg#
1 233 Metric 2003 093755 0060301-01-2001
2 233 F.Sc 2005 02147 581-Fg-2003
this is data which i store in table.
no i want to get the 2nd one row(F.Sc) fileds required
Year, Pre.Rno, Reg# for each student.
but i could not succesed becoz when i applied the query,it will gives following result.
233 2005 093755 0060301-01-2001
i want to get am get this result.
233 2005 02147 581-Fg-2003
pls helpme in that query.
regards
Shahzad
|
|
|
|
Re: Report query required. [message #341961 is a reply to message #337166] |
Wed, 20 August 2008 22:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzad-ul-hasan
Messages: 625 Registered: August 2002
|
Senior Member |
|
|
Problems:
Table Fields:
S-no NewRollno Description Year Pre.RNo Reg#
1 233 Metric 2003 093755 0060301-01-2001
2 233 F.Sc 2005 02147 581-Fg-2003
this is data which i store in table.
no i want to get the 2nd one row(F.Sc) fileds required
Year, Pre.Rno, Reg# for each student.
but i could not succesed becoz when i applied the query,it will gives following result.
233 2005 093755 0060301-01-2001
i want to get this result.
233 2005 02147 581-Fg-2003
pls helpme in that query.
regards
shahzad
|
|
|
Re: Report query required. [message #342493 is a reply to message #337166] |
Fri, 22 August 2008 09:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dude4084
Messages: 222 Registered: March 2005 Location: Mux
|
Senior Member |
|
|
Hi
Could not figure out that how come you are getting 50% of 2nd record and 50% of 1st record.
Anyhow,what i understand, your problem can be solved by this
Select *
from yourtable
where s-no = (select max(s-no) from yourtable)
Good luck
-Dude
|
|
|
Re: Report query required. [message #342674 is a reply to message #342493] |
Sat, 23 August 2008 23:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahzad-ul-hasan
Messages: 625 Registered: August 2002
|
Senior Member |
|
|
actually i am using this query;
table 1 Description:
rollno pk
name
class
table 2 description
rollno FK
pre_rollno
reg#
year
data stored in table 2:
1 2345 234-fds-2003 2003 Metric
2 234 098725-FDS-2006 2006 F.Sc
and i am using this query;
select rollno,name,class,max(prev_roll),max(reg#),max(year)
from table1, table2
where table1.rollno=table2.rollno
group by rollno,name,class;
and the result is that: (Which is not my required result)
1. 2345 098725-FDS-2006 2006
rollno for metric and reg for f.sc.
pls help me where i am wrong to get this problem out.
regards
|
|
|
|
|
Re: Report query required. [message #342875 is a reply to message #337166] |
Mon, 25 August 2008 07:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dude4084
Messages: 222 Registered: March 2005 Location: Mux
|
Senior Member |
|
|
Try following code
select rollno,name,class, prev_roll, reg#, max(year)
from table1, table2
where table1.rollno=table2.rollno
group by rollno,name,class, prev_roll, reg#;
By the way there is no s_no in your table
and
i am wondering if '#' is allowed in DDL?
-Dude
|
|
|
|
Re: Report query required. [message #345337 is a reply to message #337166] |
Wed, 03 September 2008 02:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
SELECT
T1.Year
T1.RNO
T1. Reg#
FROM TABLE1 T1
WHERE
T1.S_NO=(SELECT MAX(T2.S_NO) FROM
TABLE1 T2
WHERE T1.RNO=T2.RNO)
If there is no filed S_NO then use rowid instead of that.
|
|
|
|