Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2007
    Posts
    24

    Unanswered: SELECT TOP 2 from each details record

    Hi everyone,

    I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.

    Code:
    Example :
    =======
    Details table is as follows
    HeaderID     DetailsID
    1                    1
    1                    2
    1                    3 
    2                    4 
    3                    5
    3                    6
    3                    7
    3                    8
    
    
    output should be:
    TransDate   SupplierName   HeaderID     DetailsID
    1/1/2000         abc                 1                1
    1/1/2000         dsds                1                2
    2/3/2003         fgd                  2                4 
    2/4/2005         sdsd                3                5
    1/1/2006         fgfdg               3                6
    I am using the following query
    Code:
    SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
    Header H, Details D
    WHERE H.HeaderID = D.DetailsID
    AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)
    As I am dealing with very huge data it is taking very long time to execute.

    Is there any better way to accomplish the task?

    Thanks.

    Riyaz

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Use a join perhaps?
    Code:
    SELECT	H.TransDate,
    	H.SupplierName,
    	D.DetailsID
    FROM	Header H
    INNER JOIN Details D
    	ON H.HeaderID = D.DetailsID
    WHERE D.DetailsID IN
    	(
    	SELECT
    	TOP 2	DetailsID
    	FROM 	Details
    	WHERE 	HeaderID = H.HeaderID
    	ORDER BY <relevant field>
    	)
    WAIT
    You need an ORDER BY to use a SELECT TOP.
    Am I right?

    EDIT: due to foolishness
    Last edited by gvee; 04-24-07 at 06:02.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    24
    Hi George,

    Is there any difference between your and my query except the ORDER BY clause.

    My question is : How can I speed the task using any other logic?


    Thanks for your inputs.

    Riyaz

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There is a join instead of the where clause and there is an order by....

    TOP is meaningless without ORDER BY - what are the TOP 2 items in an unordered list?

    Try it, I think you'll find an improvement.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Posts
    24
    Hi

    I tried with join and order by clause but it is taking longer time.
    Whatever I am getting it is correct.

    Just tell me how can speed up the process.


    Riyaz

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shaikh.riyaz.a
    Just tell me how can speed up the process.
    just tell us which 2 detail rows you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... and also if you are using sql server 2000 or 2005 please
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by pootle flump
    ... and also if you are using sql server 2000 or 2005 please
    Hi

    I am using SQL Server 2000 and I want to display any 2 records against each header even if there are more than 2.


    Thanks

    Riyaz

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any two records? That's not very specific - SQL likes to be specific...
    Then again, it's your call!
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unfortunately you are going to have to decide which two

    this is exactly what we've been telling you -- "top" makes no sense in a relational database without the concept of ordering, and this can only be accomplished based on the values in some column

    perhaps you want the 2 rows with the highest DetailID values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by r937
    unfortunately you are going to have to decide which two

    this is exactly what we've been telling you -- "top" makes no sense in a relational database without the concept of ordering, and this can only be accomplished based on the values in some column

    perhaps you want the 2 rows with the highest DetailID values?
    Hi
    I got it from you only that : 'Table in relational db are unordered'
    So I want any 2 rows using TOP clause, I just want to speed up the process.
    See my first query and please tell me is there any other way than this.

    Thanks

    Riyaz

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Without an ORDER BY the query will be slower. That's another reason to add it.
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by georgev
    Without an ORDER BY the query will be slower. That's another reason to add it.
    So George should I consider your query as final solution or you can give something more?


    Riyaz

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps your original query is not performing well because of this --
    Code:
    SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
    Header H, Details D
    WHERE H.HeaderID = D.DetailsID
    AND ...
    perhaps you should run it like this instead --
    Code:
    SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
    Header H, Details D
    WHERE H.HeaderID = D.HeaderID 
    AND
    or better yet, like this --
    Code:
    SELECT H.TransDate
         , H.SupplierName
         , D.DetailsID 
      FROM Header H
    INNER
      JOIN Details D
        ON D.HeaderID = H.HeaderID 
       AND ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Posts
    24
    H.HeaderID = D.DetailsID was a typing mistake.

    But you given me two ways :
    1) H.HeaderID = D.HeaderID and
    2) D.HeaderID = H.HeaderID

    Does this make any effect on performance ?


    Riyaz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •