Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45

    Question Unanswered: Select records from two tables, plus page and order them?

    Hi,

    I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....

    DECLARE @Temp TABLE
    (
    ID INT NOT NULL,
    Name NVARCHAR(128) NOT NULL,
    Type CHAR(1) NOT NULL
    )

    INSERT INTO @Temp
    SELECT i.ID, i.Name, Type = 'I' FROM Item i
    UNION
    SELECT p.ID, p.Name, Type = 'P' FROM Package p

    SELECT * FROM @Temp
    ORDER BY Name ASC

    I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?

    Many thanks for any help!
    Sunday Ironfoot

    SQL Server 2000 n00b

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM (
       SELECT i.ID, i.Name, Type = 'I' FROM Item i
       UNION
       SELECT p.ID, p.Name, Type = 'P' FROM Package p
      ) As [x]
    ORDER BY x.name DESC
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    The trouble with that method is that I need to count the total potential records so that the calling app knows how to set up the paging links properly.

    Would your method combined with a SELECT @TotalRecordsOutput = COUNT(*) style statement be more efficient than putting all the records into a temp table and counting the total records in that temp table, before doing a ROW_NUMBER() on the temp table to ger records 1 to 10, 11 to 20 etc?
    Sunday Ironfoot

    SQL Server 2000 n00b

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Count(*)
    FROM (
       SELECT i.ID, i.Name, Type = 'I' FROM Item i
       UNION
       SELECT p.ID, p.Name, Type = 'P' FROM Package p
      ) As [x]
    ORDER BY x.name DESC
    
    SELECT TOP 10 *
    FROM (
       SELECT i.ID, i.Name, Type = 'I' FROM Item i
       UNION
       SELECT p.ID, p.Name, Type = 'P' FROM Package p
      ) As [x]
    ORDER BY x.name DESC
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Why not just capture the value of @@rowcount after the insert into the table? That way you don't have to run a separate select to determine the total rows.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was going with "why even bother inserting into the table"
    With regards to the 11th - 20th...
    Code:
    --10th to 20th
    SELECT TOP 10 *
    FROM   mytable
    WHERE  keyField NOT IN (
             SELECT TOP 10 keyField
             FROM   myTable
             ORDER
                 BY someField ASC
           )
    ORDER
        BY somefield ASC
    --For 20th to 30th change the subquery to read
    --SELECT TOP 20 keyField
    --etc
    Last edited by gvee; 02-11-08 at 10:34.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Eww sorry, that's a really aweful way of doing it
    Code:
    SELECT TOP 10 *
    FROM  (SELECT TOP 20 *
           FROM   myTable
           ORDER
               BY someField ASC
         ) As [someAlias]
    ORDER
        BY someField ASC
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TOP 10 from TOP 20 isn't going to work if both ORDER BYs are ASC

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So you ain't awe-full then eh Rudy?

    </ snicker>

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am full of awe at how quickly george can post an incorrect or incomplete answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm well learned in the art

    I just like to keep you on your toes Rudy
    George
    Home | Blog

Posting Permissions

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