locked
DISTINCT AND TOP RRS feed

  • Question

  • User629360359 posted
    ID      sendDate        username
    
    1       2/12/2011        User 1
    
    1       3/12/2011        User 2
    
    2       28/1/2012        User 1
    
    2       23/1/2012        User 1
    
    2       2/2/2012          User 2

    I want to get the distinct of the ID and display only the TOP result based on sendDate, example result:

    ID      sendDate        username
    
    1       3/12/2011        User 2
    
    2       2/2/2012         User 2

    so, how to combine the DISTINCT AND TOP into 1 query? thanks =)

    Wednesday, February 15, 2012 8:21 AM

Answers

  • User3866881 posted

    Hello again:)

    Plz have another try——

    Select * from xxx where sendDate in (select min(sendDate) from xxx group by ID)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 18, 2012 12:28 AM

All replies

  • User1950091109 posted

    select distinct top 1 username from tblName order by sendDate desc

    Wednesday, February 15, 2012 8:35 AM
  • User3866881 posted

    Hello melvitncs:)

    Group by Id first and then pick the latest date up,in the end,plz do selection——

    select * from xxx

    group by ID

    having (min(sendDate)=sendDate)

    Thursday, February 16, 2012 9:39 PM
  • User629360359 posted

    select distinct top 1 username from tblName order by sendDate desc

    nop, this query only display 1 result only.

    Hello melvitncs:)

    Group by Id first and then pick the latest date up,in the end,plz do selection——

    select * from xxx

    group by ID

    having (min(sendDate)=sendDate)

    this is not working as well =(

    Friday, February 17, 2012 2:03 AM
  • User3866881 posted

    Hello again:)

    Plz have another try——

    Select * from xxx where sendDate in (select min(sendDate) from xxx group by ID)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 18, 2012 12:28 AM
  • User-1199946673 posted

    Which result do you expect when you have another record?

    1   3/12/2011   User 1

    Saturday, February 18, 2012 5:01 AM