-1

I want to select distinct status values odered by time. However, my following query, which groups by status and orders by time, shows error like time column must be added in group by.

The query I tried is:

select "status" 
from detail_status 
where "CAId" = 'test-1234' 
group by "status" 
order by "time" desc 
limit 2;

If I add time colum in GROUP BY then the result is in wrong order. I need the above query result just grouped by status column.

Please, assist for this issue or give another idea for this query concept.

Thanks in adavance.

k_rus
  • 2,959
  • 1
  • 19
  • 31
Avz Vicky
  • 11
  • 9
  • Since you group by status, there will be several records and several time value per one status. Which time value do you want to use for sorting? You cannot use all of different values at the same time. – k_rus May 05 '21 at 09:50
  • It will be good if you provide an example of data and desired result. – k_rus May 05 '21 at 10:01

2 Answers2

0

The group by have no sense here. If you want to the 2 last status from detail_status for your table, you have to write the query like that

select "status","time" from detail_status where "CAId" = 'test-1234' order by "time" desc limit 2;

Maybe that i don't understand what you're looking for ?

Philippe
  • 1,714
  • 4
  • 17
  • status : --------- checked checked checked validate validate assume that those status value in that table, then i need result was status: -------- checked validate only, that means i need only distinct values only. – Avz Vicky May 05 '21 at 08:04
  • Can you edit your question, add a sample of datas of the table detail_status and the result you want to help us understand your need. thx – Philippe May 05 '21 at 08:39
0

To get distinct status values ordered by time it is necessary to inlude time into the result projection of the query. Since several records might correspond to a distinct value of status it is necessary to apply an aggregate function on time, e.g., MAX, AVG, or MIN. So the query might be:

SELECT "status", MAX("time") AS time
FROM detail_status 
WHERE "CAId" = 'test-1234' 
GROUP BY "status" 
ORDER BY "time" DESC 
LIMIT 2;

If you need that the final query contains only status, you can nest the above query and project only status, however the order might not be guaranteed.

k_rus
  • 2,959
  • 1
  • 19
  • 31
  • you can also use `SELECT DISTINCT ON (status) * FROM detail_status WHERE "CAId" = 'test-1234' ORDER BY status, "time" DESC;` – davidk May 05 '21 at 14:32