1

Topic: How to sort the data with null

Prompt , there is a table:
id, name, username
1, doc1, Alex
2, doc2, Kate
3, doc3, null
4, doc1, null
How it is possible to pull out such data?
name, username
doc1, Alex
doc2, Kate
doc3, null

2

Re: How to sort the data with null

day-spring;
I will consider name with NOT NULL username can to be only once. Then

SELECT NAME;
MAX (USERNAME) USERNAME
FROM TBL
GROUP BY NAME
/

SY.

3

Re: How to sort the data with null

SY wrote:

SY

At least, columns .

4

Re: How to sort the data with null

Elic wrote:

At least, columns .

Yes well?

SQL> set null null
SQL> with tbl as (
2 select 1 id, ' doc1 ' name, ' Alex ' username from dual union all
3 select 2, ' doc2 ', ' Kate ' from dual union all
4 select 3, ' doc3 ', null from dual union all
5 select 4, ' doc1 ', null from dual
6)
7 SELECT NAME;
8 MAX (USERNAME) USERNAME
9 FROM TBL
10 GROUP BY NAME
11 /
NAME USER
---- ----
doc1 Alex
doc2 Kate
doc3 null
SQL>

SY.

5

Re: How to sort the data with null

Well.

7 select max (name) as name, username from tbl group by username order by name
8 /
NAME USER
---- ----
doc1 Alex
doc2 Kate
doc3 null

6

Re: How to sort the data with null

;

1 with tbl as (
2 select 1 id, ' doc1 ' name, ' Alex ' username from dual union all
3 select 2, ' doc2 ', ' Kate ' from dual union all
4 select 5, ' doc5 ', ' Kate ' from dual union all
5 select 3, ' doc3 ', null from dual union all
6 select 4, ' doc1 ', null from dual
7)
8, t as (
9 select
10 tbl.*
11, sum (decode (username, null, 1,0)) over (order by username, id) rn
12 from tbl)
13* select * from t where rn <2
SQL> /
ID NAME USER RN
---------- ---- ---- ----------
1 doc1 Alex 0
2 doc2 Kate 0
5 doc5 Kate 0
3 doc3 1

id is not null
.....
stax

7

Re: How to sort the data with null

select name, max (username) keep (dense_rank first order by username nulls last) from tbl group by name

8

Re: How to sort the data with null

3214 sort;
For an insertion of conditions of a choice and sorting, if username a little

9

Re: How to sort the data with null

Elic;
The task though is sounded ambiguously, but proceeding from that that in an example two lines with that NAME it is more logical (IMHO) to assume grouping on NAME.
SY.

10

Re: How to sort the data with null

on id to sort a maximum it is more logical, than on username smile