Last post Oct 27, 2011 06:49 AM by danieldiesel12
Oct 27, 2011 06:30 AM|danieldiesel12|LINK
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)
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.
Oct 27, 2011 06:35 AM|DarrellNorton|LINK
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
Oct 27, 2011 06:40 AM|hans_v|LINK
SELECT * FROM TABLE WHERE ID IN (SELECT MAX(ID) FROM TABLE GROUP BY K_telefon)
Oct 27, 2011 06:43 AM|danieldiesel12|LINK
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.
Oct 27, 2011 06:49 AM|danieldiesel12|LINK
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