CODEWITHSUNDEEP
sqlpostgresql
psycho33200
psycho33200

Reputation: 31

how limit to one result per value in sql?

I am working on a view and a query resists my efforts. the goal is to find for each 'pm' which 'nom_releve' is most present and to make it go up.

I have managed so far to sort and find which is the most present. but I can not bring out ONLY the most present for each pm. I am still a beginner in SQL, I would like if possible a helping hand to unblock the situation.

Thank you all.

here is my request:

SELECT pm, nom_releve 
FROM genie_civil.v_appui 
GROUP BY pm, nom_releve 
ORDER BY pm, COUNT(nom_releve) DESC;

here is a capture of the result.

I framed a few identical 'pm', so I would only need the first result for each 'pm'

result query

Upvotes: 1

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use distinct on:

SELECT DISTINCT ON (pm) pm, nom_releve 
FROM genie_civil.v_appui 
GROUP BY pm, nom_releve 
ORDER BY pm, COUNT(nom_releve) DESC;

Upvotes: 2

Related Questions