2

I want to use distinct to display my data. But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version)

select distinct on (rule_name) 
rule_name,rule_version,polyname,state 
from rule 
order by rule_name,rule_version DESC

enter image description here

  • And why aren't you using any of those columns in your code?. Can you post the table definition, and show a sample of the result set you want? – Lamak Mar 10 '14 at 15:14
  • Could you explain your problem better? Examples of what you have and what you want to find would be good. Avoid the pictures, just write the tables. – MatheusOl Mar 10 '14 at 15:42
  • But I think my picture is very clear that someone can answer my question. – Alex Yu Mar 11 '14 at 14:11
2
SELECT distinct on (rule_name) 
  rule.rule_name,rule.rule_version,rule.polyname,rule.state, g.poly_version 
FROM  rule INNER JOIN gtest_version As g ON rule.polyname = g.polyname 
ORDER BY rule_name,g.poly_version DESC, rule_version DESC;
2

If I understand your requirements correctly, you want to join the other table on polyname and then get the max() of poly_version, like

SELECT rule_name, rule_version, polyname, state, max(poly_version)
  FROM rule r JOIN gtest_version g ON r.polyname = g.polyname
 GROUP BY rule_name, rule_version, polyname, state
 ORDER BY rule_name, rule_version DESC;

However, your query and description does not tell how you want to pick the right row from the rule table. My query will return all of these, while your DISTINCT ON will not...

  • error,polyname is ambiguous – Alex Yu Mar 11 '14 at 14:21
  • You can always try to fix it youorself. In this case you could simply pick a table alias (either r or g)... – dezso Mar 11 '14 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.