locked
Select DISTINCT and return only the lastest row RRS feed

  • Question

  • User-60784728 posted

    Hi!

    I do have a table where we add a new row for each time we are contacting a customer. For each time we will get a new status of that customer. As we add a new row each time I would like having a query that only gets the lastest status if my column of phone number exists twice. I don´t know how to do this. If I run select Distinct I can´t know which row it selects.

    My table has many columns but the main columns are:

    ID (incement by one)

    K_telefon (what is my identifier / Phone number)

    Status

    Date

    Somehow I would like quering

    select count(status) as COUNT, status from TABLE

    group by status

    But only select the latest row if K_telefon exist twice or more.

    Regards Daniel

    Thursday, October 27, 2011 6:30 AM

Answers

  • User-1199946673 posted

    SELECT * FROM TABLE WHERE ID IN (SELECT MAX(ID) FROM TABLE GROUP BY K_telefon)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 27, 2011 6:40 AM

All replies

  • User-837620913 posted

    Don't use DISTINCT.  And grouping isn't going to solve your problem.  SELECT the top 1 with an order by date descending (latest first).

    SELECT TOP 1 ID,K_telefon, Status, Date FROM TableName ORDER BY Date DESC

    Thursday, October 27, 2011 6:35 AM
  • User-1199946673 posted

    SELECT * FROM TABLE WHERE ID IN (SELECT MAX(ID) FROM TABLE GROUP BY K_telefon)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 27, 2011 6:40 AM
  • User-60784728 posted

    Hi!

    That only retrieves one record att all.

    I need to group by status so I can recieve statistics about how many customers are interessted, not interessted, buying etc.

    Thursday, October 27, 2011 6:43 AM
  • User-60784728 posted

    Thanks! That works.

    I ended up with:

    SELECT COUNT(utfall)as Antal, utfall FROM SBABBRF
    WHERE ID IN (SELECT MAX(ID) FROM SBABBRF GROUP BY K_telefon)
    group by utfall

    Thursday, October 27, 2011 6:49 AM