Home » SQL & PL/SQL » SQL & PL/SQL » query help
query help [message #445586] Tue, 02 March 2010 14:58 Go to next message
h0neydip
Messages: 3
Registered: March 2010
Junior Member
DONORNO DLNAME DFNAME DPHONE DS DCITY
---------- --------------- --------------- ---------- -- -------------
101 Abrams Louis 9018 GA London
102 Aldinger Dmitry 1521 GA Paris
103 Beckman Gulsen 8247 WA Sao Paulo
104 Berdahl Samuel 8149 WI Sydney
105 Borneman Joanna 1888 MD Bombay
106 Brock Scott 2142 AL London
107 Buyert Aylin 9355 AK New York
108 Cetinsoy Girwan 6346 AZ Rome
109 Chisholm John 4482 MA Oslo
118 Herskowitz Thomas 6872 MT London
119 Jefts Robert 8103 ME Oslo
110 Crowder Anthony 6513 NC Stockholm
111 Dishman Michelle 3903 NC Helsinki
112 Duke Peter 4939 FL Tokyo
113 Evans Ann 4336 GA Singapore
114 Frawley Todd 4785 MN Perth
115 Guo John 6247 MN Moscow
116 Hammann John 5369 ND Kabaul
117 Hays Cami 1352 SD Lima


AMOUNT YEAR DONORNO
939 1993 101
899 1993 102
111 1994 102
373 1992 101
543 1992 102
297 1993 110
84 1994 110
823 1995 110
887 1992 111
332 1993 111
882 1994 111
666 1992 112
812 1995 112
560 1994 113
223 1992 114
835 1994 114
558 1993 115
268 1993 116
345 1994 116
265 1995 116
82 1992 117
657 1995 117
17 1995 118
186 1992 119
772 1993 119
1185 1992 103
1362 1993 103
5208 1994 103
1865 1995 103
667 1993 105
60 1995 106
332 1994 107
674 1993 108
155 1994 108
838 1992 109
499 1994 109
582 1992 110


I currently have this query to list the last and first name of the donor with the least contribution amount in 1993, but its listing all the donors in that year instead of just one

SQL > select dlname, dfname, min(amount) as Amount
from donor, gift
where year = '1993'
and donor.donorno = gift.donorno
group by dlname, dfname
Re: query help [message #445589 is a reply to message #445586] Tue, 02 March 2010 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
consider including "min(amount)" as part of the WHERE clause


It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 02 March 2010 15:05]

Report message to a moderator

Re: query help [message #445600 is a reply to message #445586] Tue, 02 March 2010 21:55 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
of the donor with the least contribution


where have you specified that?
Can you please show us?

HAVING clause will solve your problem.

regards,
Delna
Re: query help [message #445622 is a reply to message #445586] Wed, 03 March 2010 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to post the content of your tables but it is highky useful to post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.


To sum up the previous posts: "having min(year) = 1993".
Note that YEAR is a reserved word and should not be used as column name.


Regards
Michel
Re: query help [message #445632 is a reply to message #445586] Wed, 03 March 2010 01:10 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
h0neydip wrote on Tue, 02 March 2010 21:58
I currently have this query to list the last and first name of the donor with the least contribution amount in 1993, but its listing all the donors in that year instead of just one

SQL > select dlname, dfname, min(amount) as Amount
from donor, gift
where year = '1993'
and donor.donorno = gift.donorno
group by dlname, dfname

Do you want donor with the least contribution in one gift or the least total contribution (in that year)?
What shall be the result for given sample (by the way because of amount and untidiness of rows, its meaning is undecipherable) and why?

It looks to me like classical TOP N query, which implementation is described e.g. here (just follow the link, choose the one for your Oracle version).
Previous Topic: Program Header
Next Topic: I wan to find the biggest age number
Goto Forum:
  


Current Time: Wed Sep 28 05:44:31 CDT 2016

Total time taken to generate the page: 0.11282 seconds