[SQL] how to query and group by specific attribute?

Question

I have some data that store in my DB such as:

id    nid    name  
1     111    john
2     111    bill
3     222    tom
4     111    boy
5     111    girl
6     111    tim
7     333    jone
8     222    mike
.      .      .

and I need to query all id and then grouping all by nid. the expected result is

id    nid    name  
1     111    bill
3     222    tom
6     111    tim
7     333    jone
8     222    mike

how can i do in one query or any idea?

Answer 1

Woah, how do you get that result? I don't see any real correlation between the original data and the resulting data. If you were to group by NID, you'd need an aggregate function afterwards, such as COUNT() or SUM(), etc. Also, if a record is in the DB as 1|111|john, how could you expect 1|111|bill? The ID should, I hope, stay with john. Hope I can help.

Answer 2

When you group and you wan't to return data from fields that you don't group by, you have to decide which value you want from each group. You use delegates like min and max to determine this (and for numerical data there are also delegates like avg and count).

For example, this query:

select min(id), nid, min(name)
from theTable
group by nid
order by nid

would give you this result:

id    nid    name  
1     111    bill
3     222    mike
7     333    jone

Note that you only get one group for each value in the nid column. Records are grouped based on the value only, not by pairing adjacent records.

If you want exactly the result in your example, grouping is not what you would use. You would have to use a cursor so that you can step through the records and decide for each what to do with it.

Answer 3

Assuming the typo in the user's first row of results (id = 2, not id = 1), and using Oracle analytics:

SQL> select * from t1;
        ID        NID NAME
---------- ---------- ------------------------------
         1        111 john
         2        111 bill
         3        222 tom
         4        111 boy
         5        111 girl
         6        111 tim
         7        333 jone
         8        222 mike
SQL> select id, nid, name
  2    from (select id, nid, name,
  3                 lead(nid) over (order by id) as next_nid
  4            from t1 )
  5   where next_nid <> nid
  6      or next_nid is null;
        ID        NID NAME
---------- ---------- ------------------------------
         2        111 bill
         3        222 tom
         6        111 tim
         7        333 jone
         8        222 mike