Home » Developer & Programmer » Reports & Discoverer » Report query required. (3.0)
Report query required. [message #337166] Wed, 30 July 2008 01:34 Go to next message
shahzad-ul-hasan
Messages: 464
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 #337175 is a reply to message #337166] Wed, 30 July 2008 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, take some time and read the OraFAQ Forum Guide; pay attention to "How to format your post" section. Format your message using the [code] or [pre] tags so that columns are properly aligned and easier to understand.

If necessary, provide CREATE TABLE and INSERT INTO sample data statements so that we could reproduce your case in our databases. Finally, saying that "query doesn't work" means nothing as you didn't attach the query. All we can do is guess, but - I'm not going to do that.

Shortly: provide properly formatted information, your query and someone will probably take a look.
Re: Report query required. [message #341961 is a reply to message #337166] Wed, 20 August 2008 22:34 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
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 messageGo to next message
dude4084
Messages: 221
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 messageGo to next message
shahzad-ul-hasan
Messages: 464
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 #342675 is a reply to message #337166] Sun, 24 August 2008 00:04 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi

what is your required result?


do you want to choose on the basis of maximum serial no for each student?


-Dude
Re: Report query required. [message #342693 is a reply to message #342675] Sun, 24 August 2008 04:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
yes
i need the maximum result on the basis of s-no.?
1 2345 23423-Fds-2003 2003 Metric
2 234 098-FDS-2006 2006 F.Sc
i need the second row(F.Sc)?

send me the query?

regards
Re: Report query required. [message #342875 is a reply to message #337166] Mon, 25 August 2008 07:45 Go to previous messageGo to next message
dude4084
Messages: 221
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 #343023 is a reply to message #342875] Mon, 25 August 2008 23:20 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
Yes it is not allowed in DDL?

i find thw one clu. s-no which is not addedin query.pls send me the correct queruy, how ican get this required result.
regards
Re: Report query required. [message #345337 is a reply to message #337166] Wed, 03 September 2008 02:20 Go to previous messageGo to next message
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.
Re: Report query required. [message #346171 is a reply to message #345337] Sat, 06 September 2008 23:06 Go to previous message
shahzad-ul-hasan
Messages: 464
Registered: August 2002
Senior Member
sir
send me the how i can use rowid.send me the query of rowid.
regards
Shahzad
Previous Topic: REP-1314 Format Trigger 'xxx' references column 'zzz' at a wrong frequency
Next Topic: SerialNo
Goto Forum:
  


Current Time: Sat Dec 10 22:36:09 CST 2016

Total time taken to generate the page: 0.11045 seconds