Can't get unique values using a postgres query

Question

I have the a postgre sql query as follows

SELECT cNo,max(numLogs),name,surname FROM details GROUP BY cNo,name,surname

and if I run this my results don't group cNo instead, is there any way I can get the cNo unique/grouped.

I get the results similar to the following

cNo         numLogs     name        surname
23          43          asfas       safdasd
23          45          dsfds       fdsfsdfsd
23          43          dsfsd       dsfsdfsd

and I want something like

cNo         numLogs     name        surname
23          45          asfas       safdasd

Sean Vieira · Accepted Answer · 2011-01-05 15:39:33Z

4

If you only want the unique cNo's and you don't care about which name / surname you are pulling you can use another aggregate function on those fields.

SELECT cNo
    ,max(numLogs)
    ,max(name)
    ,max(surname)
-- min would also work.
FROM details 
GROUP BY cNo

Otherwise, what you are pulling is each unique combination of cNo, name, and surname.

If you need to pull a particular name or surname (that won't be pulled by min, max or any of PostGreSQL's aggregate functions), then you'll want to filter your results rather than selecting everything from the table.

answered Jan 5, 2011 at 15:39

Sean Vieira

161k34 gold badges321 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can't get unique values using a postgres query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related