| Incorporate in single SQL query. Please reply immediately [message #2623] |
Wed, 31 July 2002 14:00  |
Prasanna Jagatap
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
I am having following data in my table.
Dwg Csttype cost
12345 SG 100
12345 AG 200
12345 AE 150
12345 SE 100
12344 SG 0
12344 AG 100
12344 AE 200
12344 SE 0
12333 SG 0
12333 AG 0
12333 SE 100
12333 AE 200
Now I want a single record for each dwg number.
For that particular dwg number if cost has some value other than 0 for csttype SG then I will take the record whose csttype is SG & wil discard all other records for that dwg number. If the cost is 0 for csttype SG then, I will check cost for csttype AG if its other than 0 i will take that record & will discard others for that dwg number. If cost is 0 for both SG & AG csttype then I will take the AE record.
I want everything should be incorporated in only one query. So for the above table my target table will be
Dwg Csttype cost
12345 SG 100
12344 AG 100
12333 SE 100
|
|
|
|
| Re: Incorporate in single SQL query. [message #2625 is a reply to message #2623] |
Wed, 31 July 2002 14:37  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Please do not double-post similar questions - you posted a simpler form of your question earlier.
You give no requirements for a csttype of SE, but from your sample result set, I assume that it comes between AG and AE.
sql>select dwg, csttype, cost
2 from t
3 where (dwg, csttype) in
4 (select dwg, max(csttype)
5 from t
6 where cost > 0
7 group by dwg)
8 order by dwg desc;
DWG CS COST
--------- -- ---------
12345 SG 100
12344 AG 100
12333 SE 100 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
