OraFAQ Forum: SQL & PL/SQL » singly sql query Please reply immediately

Home » SQL & PL/SQL » SQL & PL/SQL » singly sql query Please reply immediately

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

singly sql query Please reply immediately [message #2606]

Tue, 30 July 2002 19:58

Prasanna Jagatap
Messages: 3
Registered: July 2002

Junior Member

I am having following data in my table.
Dwg Csttype
12345 SG
12345 AG
12345 AE
12344 AG
12344 AE
12333 AE
Now I want a single record for each dwg number.
For that particular dwg number if csttype SG is present then I want SG value & discard all other values. If SG is not present then it should check for AG value & take that record, discard all other record.
If both values are not present then it should take AE record. I want everything should be incorporated in only one query. So for the above table my target table will be

Dwg Csttype
12345 SG
12344 AG
12333 AE

Report message to a moderator

Re: singly sql query Please reply immediately [message #2607 is a reply to message #2606]

Tue, 30 July 2002 20:45

Anirudh Sharma
Messages: 8
Registered: July 2002

Junior Member

Hi
I have made the foll. table
DWG CS
------ --
12345 SG
12345 AG
12345 AE
12344 AG
12344 AE
12346 AE

and the query shows the result

SELECT * FROM AN WHERE CSTTYPE = 'SG' UNION SELECT * FROM AN WHERE CSTTYPE = 'AG' AND DWG NOT IN
(SELECT DWG FROM AN WHERE CSTTYPE = 'SG') UNION SELECT * FROM AN WHERE CSTTYPE = 'AE' AND DWG NOT IN
(SELECT DWG FROM AN WHERE CSTTYPE = 'SG' UNION SELECT DWG FROM AN WHERE CSTTYPE = 'AE')

I have not checked it thoroughly but it is working on the existing data in the table

Report message to a moderator

Re: singly sql query Please reply immediately [message #2619 is a reply to message #2606]

Wed, 31 July 2002 08:43

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Much easier than you think:

sql>select * from t order by dwg desc;
 
      DWG CS
--------- --
    12345 SG
    12345 AG
    12345 AE
    12344 AG
    12344 AE
    12333 AE
 
6 rows selected.
 
sql>select dwg, max(csttype) csttype
  2    from t
  3   group by dwg
  4   order by dwg desc;
 
      DWG CS
--------- --
    12345 SG
    12344 AG
    12333 AE

Report message to a moderator

Previous Topic:	Creating a temp table and unique numbering
Next Topic:	SQL query - count

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:12:49 CDT 2024