Post reply

getting unique record

  • July 15, 2010 at 4:13 am

    #235315

    I am trying to run a query

    select distinct e.equipmentnumber,c.createdate from equipment e

    inner join configurations c on e.rifgeneratedid=c.rifgeneratedid

    where c.createdate > '01/01/2010' and c.createdate < '07/14/2010'

    but getting the duplicate equipmentnumbers as the date is different. Can anyone tell me how to get only 1 record per equipment.

    Thanks in advance

    Regards,

    Naveen

  • July 15, 2010 at 4:25 am

    #1193344

    Can u pls tell what createdate u want.... Even u can try with max/min in ur query... depending upon the requirement...

  • July 15, 2010 at 5:02 am

    #1193367

    Hi Naveen,

    You can use both below queries

    select

    e.equipmentnumber,

    MAX(c.createdate) createdate

    from equipment e

    inner join configurations c on e.rifgeneratedid=c.rifgeneratedid

    where

    c.createdate > '01/01/2010' and

    c.createdate < '07/14/2010'

    group by e.equipmentnumber

    ;WITH CTE AS (

    select

    e.equipmentnumber,

    c.createdate,

    rn = ROW_NUMBER() OVER (ORDER BY c.createdate)

    from equipment e

    inner join configurations c on e.rifgeneratedid=c.rifgeneratedid

    where

    c.createdate > '01/01/2010' and

    c.createdate < '07/14/2010'

    )

    SELECT equipmentnumber, createdate from cte where rn = 1

    You can use the second query Row_Number() function for many other cases although in this case MIN or MAX according to your requirement is enough

    I hope it helps,

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 15, 2010 at 5:05 am

    #1193368

    Thanks for your response. Now its resolved

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply