Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with a query plz

Author  Topic 

ann06
Posting Yak Master

171 Posts
Posted - 2009-08-20 : 02:43:27
i have a query that returns records as follows
this is a simpler version result


docno,projectno, dwgno, revno, subject
111,666,444,4,test
112,666,333,2,first one
113,666,333,2,duplicate record

i want the last two records not to appear both only to have one of them as i want only distinct projectno,dwgno,revno
i tried to max(docno) but its taking more than 20 sec before was 4 secds

how to fix this? (i want any docno to appear as long i have the result distinct)

orginal query as follows
----------------------------------------

SELECT DISTINCT
CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD2_AR as projectno, p.CHAR_FIELD1 as dwgno, p.REVISION_NO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,
J.DESCRIPTION AS [Section], v.DESCRIPTION AS doc_kind, p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', 'A',
'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', '16', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')


GROUP BY char_field1, char_field2_ar, subcat_id) b ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND
p.REVISION_NO IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', 'A', 'B', 'C', 'D',
'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', '16', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z') INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP v ON CAST(p.CAT_ID AS varchar) = v.ID
--and p.char_field2_ar='48531' and char_field1='m001' and t.description='soft drawing'
----------------------------------------

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-08-20 : 04:22:48
First we will display formatted code (using [C0DE][/C0DE]):
SELECT DISTINCT Cast(p.indocno AS INT) AS indocno, 
                p.char_field2_ar       AS projectno, 
                p.char_field1          AS dwgno, 
                p.revision_no, 
                p.char_field3, 
                p.char_field7_ar, 
                t.DESCRIPTION, 
                j.DESCRIPTION          AS [Section], 
                v.DESCRIPTION          AS doc_kind, 
                p.subject 
FROM   dbo.technical_main p 
       INNER JOIN (SELECT   Max(revision_no) d, 
                            char_field1      c, 
                            char_field2_ar   e, 
                            subcat_id        j 
                   FROM     technical_main m 
                   WHERE    revision_no IN ('0','1','2','3', 
                                            '4','5','6','7', 
                                            '8','9','10','11', 
                                            '12','13','14','15', 
                                            '16','17','18','19', 
                                            '20','21','22','23', 
                                            '24','25','A','B', 
                                            'C','D','E','F', 
                                            'G','H','I','J', 
                                            'K','L','M','N', 
                                            'O','P','16','Q', 
                                            'R','S','T','U', 
                                            'V','W','X','Y', 
                                            'Z') 
                   GROUP BY char_field1, 
                            char_field2_ar, 
                            subcat_id) b 
         ON p.revision_no = b.d 
            AND p.char_field1 = b.c 
            AND p.char_field2_ar = b.e 
            AND p.revision_no IN ('0','1','2','3', 
                                  '4','5','6','7', 
                                  '8','9','10','11', 
                                  '12','13','14','15', 
                                  '16','17','18','19', 
                                  '20','21','22','23', 
                                  '24','25','A','B', 
                                  'C','D','E','F', 
                                  'G','H','I','J', 
                                  'K','L','M','N', 
                                  'O','P','16','Q', 
                                  'R','S','T','U', 
                                  'V','W','X','Y', 
                                  'Z') 
       INNER JOIN dbo.cust_hierarchy_lookup t 
         ON p.char_field7_ar = t.id 
       INNER JOIN dbo.cust_hierarchy_lookup j 
         ON p.char_field3_ar = j.id 
            AND p.subcat_id = b.j 
       INNER JOIN dbo.cust_hierarchy_lookup v 
         ON Cast(p.cat_id AS VARCHAR) = v.id 
                                        --and p.char_field2_ar='48531' and char_field1='m001' and t.description='soft drawing'

Next step is to have a look what we can do.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-08-20 : 04:32:31
Maybe this:
select          max(indocno)        as indocno, 
                projectno, 
                dwgno, 
                revision_no, 
                max(char_field3)    as char_field3, 
                max(char_field7_ar) as char_field7_ar, 
                max([DESCRIPTION])  as [DESCRIPTION], 
                max([Section])      as [Section], 
                max(doc_kind)       as doc_kind, 
                max([subject])      as [subject]
from       
(
SELECT DISTINCT Cast(p.indocno AS INT) AS indocno, 
                p.char_field2_ar       AS projectno, 
                p.char_field1          AS dwgno, 
                p.revision_no, 
                p.char_field3, 
                p.char_field7_ar, 
                t.DESCRIPTION, 
                j.DESCRIPTION          AS [Section], 
                v.DESCRIPTION          AS doc_kind, 
                p.subject 
FROM   dbo.technical_main p 
       INNER JOIN (SELECT   Max(revision_no) d, 
                            char_field1      c, 
                            char_field2_ar   e, 
                            subcat_id        j 
                   FROM     technical_main m 
                   WHERE    revision_no IN ('0','1','2','3', 
                                            '4','5','6','7', 
                                            '8','9','10','11', 
                                            '12','13','14','15', 
                                            '16','17','18','19', 
                                            '20','21','22','23', 
                                            '24','25','A','B', 
                                            'C','D','E','F', 
                                            'G','H','I','J', 
                                            'K','L','M','N', 
                                            'O','P','16','Q', 
                                            'R','S','T','U', 
                                            'V','W','X','Y', 
                                            'Z') 
                   GROUP BY char_field1, 
                            char_field2_ar, 
                            subcat_id) b 
         ON p.revision_no = b.d 
            AND p.char_field1 = b.c 
            AND p.char_field2_ar = b.e 
            AND p.revision_no IN ('0','1','2','3', 
                                  '4','5','6','7', 
                                  '8','9','10','11', 
                                  '12','13','14','15', 
                                  '16','17','18','19', 
                                  '20','21','22','23', 
                                  '24','25','A','B', 
                                  'C','D','E','F', 
                                  'G','H','I','J', 
                                  'K','L','M','N', 
                                  'O','P','16','Q', 
                                  'R','S','T','U', 
                                  'V','W','X','Y', 
                                  'Z') 
       INNER JOIN dbo.cust_hierarchy_lookup t 
         ON p.char_field7_ar = t.id 
       INNER JOIN dbo.cust_hierarchy_lookup j 
         ON p.char_field3_ar = j.id 
            AND p.subcat_id = b.j 
       INNER JOIN dbo.cust_hierarchy_lookup v 
         ON Cast(p.cat_id AS VARCHAR) = v.id 
)dt
group by projectno,dwgno,revision_no


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

ann06
Posting Yak Master

171 Posts
Posted - 2009-08-20 : 05:14:06
i fixed using better version i inserted the max(docno) in the subquery like this thanks for your support

SELECT DISTINCT
CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,
J.DESCRIPTION AS [Section], v.DESCRIPTION AS doc_kind,k.description as doc_Action, p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT max(indocno) as indocno,MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', 'A',
'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', '16', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')
GROUP BY char_field1, char_field2_ar, subcat_id) b ON
b.indocno=p.indocno INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j INNER JOIN
dbo.CUST_HIERARCHY_LOOKUP v ON CAST(p.CAT_ID AS varchar) = v.ID inner join
dbo.cust_hierarchy_lookup k on cast(p.subcat_id as varchar)=k.id
Go to Top of Page

ann06
Posting Yak Master

171 Posts
Posted - 2009-08-20 : 05:16:06
but the //
not changed anything!!
Go to Top of Page

ann06
Posting Yak Master

171 Posts
Posted - 2009-08-20 : 05:17:45
code /code i meant
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-08-20 : 07:58:58
I have first formatted your code using:
http://www.dpriver.com/pp/sqlformat.htm
and then pasted here between [C0DE] and [/C0DE]

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site