Search code examples
sqlgroup-byderby

How to group rows and select a "sample" match in one go

I have a table in a Derby database of the form of

Status   Name
0        Name1
0        Name2
1        Name3
1        Name4
2        NameX
...

I use the following to summarize how often each status happens

SELECT COUNT(*), STATUS
FROM MYTABLE
GROUP BY STATUS

Now I would like to add one sample-name for each status (does not matter which one), i.e. I would like a result similar to

Count    Status     Sample
2        0          Name2
2        1          Name3
1        2          NameX
...

I tried using sub-query together with ROW_NUMBER(), but couldn't get it to work.

So is there a way to write a SQL statement which includes one name from the data as shown?

Solution

  • Are you trying to make the names "random", if not, you could try 

    select count(*), max(Name), Status
from MYTABLE
group by Status