Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: latest date using join

    How do you get firstname, lastname for the latest DATE?
    Thanks.

    create table computert1 (id int identity (1,1), firstname varchar(20), lastname varchar(20))
    insert computert1 (firstname, lastname)
    select 'daniel', 'wade' union all
    select 'joe', 'walker' union all
    select 'daniel', 'serious' union all
    select 'sam', 'jordan'

    create table computert2 (id int identity (1,1), dates datetime)
    insert computert2 (dates)
    select getdate() union all
    select '2008-10-01' union all
    select '2005-10-01'
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not possible with your design.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by pootle flump View Post
    Not possible with your design.
    so in identity we cant make a relation if the id column is a int or varchar then how can we build the relation or get the o/p
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't define a relationship (totally different to a relation) between two identity columns.

    Was your intention that the two id columns were related?
    What version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    it was sql server 2005
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    create table computert1 (id int identity (1,1), firstname varchar(20), lastname varchar(20))
    insert computert1 (firstname, lastname)
    select 'daniel', 'wade' union all
    select 'joe', 'walker' union all
    select 'daniel', 'serious' union all
    select 'sam', 'jordan'
    
    create table computert2 (id int identity (1,1), dates datetime)
    insert computert2 (dates)
    select getdate() union all
    select '20081001' union all
    select '20051001'
    
    SELECT  computert1.firstname
          , computert1.lastname
    FROM    dbo.computert1
    INNER JOIN 
            (
                SELECT  id
                      , date_ordinal        = ROW_NUMBER()  OVER(ORDER BY dates DESC)
                FROM    dbo.computert2
            ) AS computert2
    ON  computert2.id           = computert1.id
    AND computert2.date_ordinal = 1
    
    DROP TABLE computert1, computert2
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    that's nice getting the record but it does not shows the date column ???
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's because you asked for lastname and firstname, no mention of returning the date.

    You'll need to tinker with the SQL - it is not difficult.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by pootle flump View Post
    That's because you asked for lastname and firstname, no mention of returning the date.

    You'll need to tinker with the SQL - it is not difficult.
    ok pootle i will try it
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

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