Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Must be a better way (select on a view on a view)

    Being only a part time SQL coder, I've been blundering on, however when I did a select on a view which was a select on a view, I thought there must be a better way.

    I have a table ...

    CREATE TABLE [dbo].[Test_Table](
    [uid] [int] IDENTITY(1,1) NOT NULL,
    [textcol] [nvarchar](20) NULL,
    [intcol] [int] NULL,
    [textcol2] [nchar](10) NULL,
    [textcol3] [nchar](10) NULL
    )

    with contents of

    uid textcol intcol textcol2 textcol3
    1 a 1 asd f1
    2 a 2 sdf c1
    3 b 7 dfg f1
    4 c 4 fgh f1
    5 c 4 ghj c1
    6 b 3 hjk c1
    7 a 1 jkl f1
    8 c 3 kl; c1


    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (1, "a", 1, "asd", "f1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (2, "a", 2, "sdf", "c1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (3, "b", 7, "dfg", "f1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (4, "c", 4, "fgh", "f1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (5, "c", 4, "ghj", "c1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (6, "b", 3, "hjk", "c1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (7, "a", 1, "jkl", "f1")
    INSERT INTO [dbo].[Test_Table] ([textcol],[intcol],[textcol2],[textcol3])
    VALUES (8, "c", 3, "kl;", "c1")


    Columns 4 and 5 are info only, and 1 is just an identifier. What I want as a result is

    textcol, intcol, textcol2, textcol3
    -------------------------------
    "a", 2, "sdf", "c1"
    "b", 7, "dfg", "f1"
    "c", 4, "ghj", "c1"

    ie a unique column 2, with the highest column 3 and the relavant cols 4 and 5 for that record. For "c" this gives 2 results so I'm taking the latest i.e the one with the highest uid (col1)

    Hope I'm making sense so far.

    What I've done is to create a view

    CREATE VIEW [dbo].[tt1]
    AS
    SELECT textcol, MAX(intcol) AS Expr1
    FROM dbo.Test_Table
    GROUP BY textcol

    then I created another view

    CREATE VIEW [dbo].[tt2]
    AS
    SELECT dbo.tt1.textcol, dbo.Test_Table.intcol, MAX(dbo.Test_Table.uid) AS Expr1
    FROM dbo.tt1 LEFT OUTER JOIN
    dbo.Test_Table ON dbo.Test_Table.textcol = dbo.tt1.textcol AND dbo.Test_Table.intcol = dbo.tt1.Expr1
    GROUP BY dbo.tt1.textcol, dbo.Test_Table.intcol

    and then finally my select statement

    select test_table.textcol, test_table.intcol, test_table.textcol2, test_table.textcol3 from test_table
    inner join tt2 on test_table.textcol = tt2.textcol and test_table.intcol = tt2.intcol and test_table.uid = tt2.expr1

    This gives the result I want but must be slow and / or daft! Surely there must be a better way. I'm using sql server 2005. Any hints or tips gratefully received.

    Thanks
    Rachel

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Not sure if I covered everything there, but do you mean something like:
    Code:
    select textcol
         , intcol
         , textcol2
         , textcol3
       from testtable tt
    where tt.intcol = (select max(tt1.intcol)
                          from testtable tt1
                       where tt.textcol = tt1.textcol)
      and tt.uid    = (select max(tt2.uid)
                          from testtable tt2
                       where tt.textcol = tt2.textcol
                         and tt.intcol  = tt2.intcol)
       and ?????
    Dave

  3. #3
    Join Date
    Sep 2009
    Posts
    21
    Magic! Thanks a lot for that. I knew I was over complicating it.
    Thanks

Posting Permissions

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