| Author |
Topic |
|
mary H
Starting Member
32 Posts |
 Posted - 2008-10-09 : 04:41:11
|
| Ive created a table called Lessons. I have two columns lesson_id and money. The table looks like this: lesson_id / place / money 21 / 1 / 125221 / 3 / 124056 / 1 / 120056/ 2 / 582056 / 3 / 5750I need to create a subquery.For each lesson i need to list the money available for first,second and thirdI've created this code: SELECT lesson_id, (SELECT MAX(money)) AS first, (select MIN(money)) AS third from lessons group by lesson_id I need help in creating a code to get the 'SECOND' place money... |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-10-09 : 04:49:03
|
| if, as according to your other post, you are using MySQL the same advice applies. you may be best posting in a MySQL ForumEm |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-09 : 05:12:32
|
in sql server, you can do this using below querySELECT lesson_id,MAX(CASE WHEN place=1 THEN [money] ELSE NULL END) AS First,MAX(CASE WHEN place=2 THEN [money] ELSE NULL END) AS Second,MAX(CASE WHEN place=3 THEN [money] ELSE NULL END) AS ThirdFROM LessonsGROUP BY lesson_id |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-09 : 06:21:26
|
GREAT, IT WORKS  Thanks visakh16! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-09 : 06:26:47
|
quote:
Originally posted by mary H
GREAT, IT WORKS Thanks visakh16!
welcome |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 06:52:00
|
| why are you editing your original posts? |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:02:38
|
| Im just trying the codes with different data.. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-10 : 07:05:35
|
You do know the difference between a [correlated] subquery and derived table?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 07:06:11
|
quote:
Originally posted by mary H
Im just trying the codes with different data..
what codes? i didnt get you. where you trying to post a new question? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-10 : 07:09:29
|
I am stumped over the fact every question she makes has "subquery" in the title.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:09:38
|
quote:
Originally posted by Peso
You do know the difference between a [correlated] subquery and derived table?
E 12°55'05.63"N 56°04'39.26"
where the sub-query produces a variable result, or different result depending on a value in the row of the outer query?? |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:10:40
|
quote:
Originally posted by visakh16
quote:
Originally posted by mary H
Im just trying the codes with different data..
what codes? i didnt get you. where you trying to post a new question?
sorry, the sql query |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:11:43
|
quote:
Originally posted by Peso
I am stumped over the fact every question she makes has "subquery" in the title.
E 12°55'05.63"N 56°04'39.26"
Thats what i need to get the query results |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 07:12:32
|
quote:
Originally posted by mary Hsorry, the sql query
does that mean solution provided was not the one you wanted? or were you asking another related question? if yes, post as a new reply and dont edit the original post. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-10 : 07:15:58
|
quote:
Originally posted by mary H
Thats what i need to get the query results
Is using a [correlated] subquery a requirement?Me and Visakh16 has shown several suggestions without a subquery.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:16:11
|
quote:
does that mean solution provided was not the one you wanted? or were you asking another related question? if yes, post as a new reply and dont edit the original post.
No, the solution you provided worked and was what i wanted. But what im trying to do is change the data i provided to check if the solution u provided works with my other tables. |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:18:08
|
quote:
Originally posted by Peso
quote:
Originally posted by mary H
Thats what i need to get the query results
Is using a [correlated] subquery a requirement?Me and Visakh16 has shown several suggestions without a subquery.
E 12°55'05.63"N 56°04'39.26"
No its not a requirement. But i thought i needed a subquery to achieve the results |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 07:21:17
|
quote:
Originally posted by mary HNo, the solution you provided worked and was what i wanted. But what im trying to do is change the data i provided to check if the solution u provided works with my other tables.
why change it in post? change and try query in your system and check if it works. |
|
|
|
mary H
Starting Member
32 Posts |
Posted - 2008-10-10 : 07:25:37
|
quote:
Originally posted by visakh16
quote:
Originally posted by mary HNo, the solution you provided worked and was what i wanted. But what im trying to do is change the data i provided to check if the solution u provided works with my other tables.
why change it in post? change and try query in your system and check if it works.
Okay, sorry i thought its easier |
|
|
|
|
Creating a Subquery