Results 1 to 10 of 10

Thread: T-SQL Problem

  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: T-SQL Problem

    Ok, this is my first post ....

    I am trying to find some working code which will allow me to compare the first record in a table with the last record in the same table.

    The fields are all char(s)

    I want to be able to specifically find when one field called Name1 = Name2, but only for the First and Last Rows

    Any ideas ?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    What do you mean with 'first record' and 'last record/s'?

  3. #3
    Join Date
    Sep 2004
    Posts
    6
    The first record which is returned that has Name1 = Name2 and the last record which has Name1=Name2

    Example

    RecNo Name1 Name2 Address1 Address2
    1 Jones Jones City1 City1
    2 Smith Davis City1 City2
    3 Hughes Jones City3 City1
    4 Jones Jones City1 City1
    5 Hall Border City4 City1
    6 Jones Jones City1 City1


    From this I would want to see, as First and Last Record have matching Name1 = Name2
    1 Jones Jones City1 City1
    6 Jones Jones City1 City1



    If I had another record


    RecNo Name1 Name2 Address1 Address2
    7 Smith Jones City1 City1



    I would not want to see any results, as the First and Last Record do not have Name1 = Name2

    Does that make sense ?

    Thanks for your help

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    How does this work?

    use monkey
    go

    set nocount on

    create table tab1 (
    RecNo integer
    , Name1 varchar(10)
    , Name2 varchar(10)
    , Address1 varchar(10)
    , Address2 varchar(10)
    )
    go

    insert into tab1 (RecNo, Name1, Name2, Address1, Address2)
    select 1, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 2, 'Smith', 'Davis', 'City1', 'City2'
    union
    select 3, 'Hughes', 'Jones', 'City3', 'City1'
    union
    select 4, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 5, 'Hall', 'Border', 'City4', 'City1'
    union
    select 6, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 7, 'Smith', 'Jones', 'City1', 'City1'
    go

    select *
    from tab1

    select min(t1.recno) [min_recno], t2.max_recno
    from tab1 t1
    , (select max(recno) [max_recno], name1, name2 from tab1 group by name1, name2) as t2
    where t1.name1 = t2.name1
    and t1.name2 = t2.name2
    and t1.recno <> t2.max_recno
    group by t2.max_recno

    go


    drop table tab1
    go

  5. #5
    Join Date
    Sep 2004
    Posts
    6
    Ok, I'm impressed

    That seems to work upto a point, it does pull out Record 1 and 6, but if Record 7 is added, I would need it to return no value as Record 7 Name1 = Name2 is not True as Record 1 and Record 7 differ.

    Thanks again

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    I'm not sure what you mean, when I run the query below, RecNo 7 is not shown eventhough it is inserted into tab1.

  7. #7
    Join Date
    Sep 2004
    Posts
    6
    Sorry

    What I mean to say was that only a resultset should be returned if the First and Last record have the same Name1=Name2

    So if Record 6 was the last then Jones=Jones which would return a match

    If Record 7 was the last then Jones=Smith would not match and should not return anything

    Hope that makes sense

    Thanks again

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    AAARRGGHHH!

    Why, Martin! Why?

    I suspect that if you explained the purpose of this exercise somebody on thsi forum might be able to suggest a better method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Posts
    492
    ok, so this is ugly...

    but I think it works...



    use monkey
    go

    set nocount on

    create table tab1 (
    RecNo integer
    , Name1 varchar(10)
    , Name2 varchar(10)
    , Address1 varchar(10)
    , Address2 varchar(10)
    )
    go

    insert into tab1 (RecNo, Name1, Name2, Address1, Address2)
    select 1, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 2, 'Smith', 'Davis', 'City1', 'City2'
    union
    select 3, 'Hughes', 'Jones', 'City3', 'City1'
    union
    select 4, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 5, 'Hall', 'Border', 'City4', 'City1'
    union
    select 6, 'Jones', 'Jones', 'City1', 'City1'
    union
    select 7, 'Smith', 'Jones', 'City1', 'City1'
    go

    select *
    from tab1

    select min(t1.recno) [min_recno], t2max, t3max
    into #temp
    from tab1 t1
    , (select max(recno) [t2max], name1, name2 from tab1 group by name1, name2) as t2
    , (select max(recno) [t3max], name1, name2 from tab1 group by name1, name2) as t3
    where 1 = 1
    and t1.name1 = t2.name2
    and t1.name1 = t3.name1
    and t1.name2 = t3.name2
    group by t2max, t3max
    go

    select * from #temp
    go

    select #t.min_recno, max(#t.t2max), max(#t.t3max)
    from #temp #t
    , (select min_recno, max(t2max) [t1_max] from #temp group by min_recno) t1
    where #t.min_recno = t1.min_recno
    and #t.t3max = t1.t1_max
    group by #t.min_recno


    drop table #temp
    go

    drop table tab1
    go

  10. #10
    Join Date
    Sep 2004
    Posts
    6
    Many thanks
    I shall give that a go

Posting Permissions

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