select col_name from tab_name group by col_name asc;
group by 可以解决你的问题了!
group by 是默认按照第一列排序的...
select col_name from table_name t1 where exists ( select col_name,min(rowid) from table_name t2 group by col_name having min(rowid) = t1.rowid )
18:48:09 SQL> select * from test;ID NAME ----- ---------- 2 drop 1 cat 1 hat 3 gg 2 haveElapsed: 00:00:00.00 18:51:30 SQL> select * from test t1 where exists (select id,min(rowid) from test t2 group by id havi ng min(rowid) = t1.rowid);ID NAME ----- ---------- 2 drop 1 cat 3 ggElapsed: 00:00:00.00
select distinct xx,... from 表名 order by xx desc
select * from (select a.* ,rank() over (partition by c1/*your columnname*/ order by c2/*primary key*/) rk from testa a) where rk=1;如果你有一个primary _id列的话可以按这个方法试一下应该可以实现的!
换成这个 select * from test t1 where rowid in (select min(rowid) from test t2 group by id);
刚才用explain plan看了下,第一次用的方法做了两次全表扫描,是很慢的 后面的方法因为是full scan by rowid,所以速度有质的飞跃
不行啊!在里面用group by以后,它是先排序在得出min(rowid)
rowid代表了记录在表中的位置,不受到group by影响的
A rowid is unique for a row within a table, but not within a database. A rowid consists of three parts: file id, block number and slot in this block. As a slot can be occupied at most by one row, a rowid uniquely identifies a row in a table.
select min(rowid) from test t2 group by id 这个subquery得出来的结果是按照id排序的,但是不影响最后的结果,因为外面用的是in
可以解决你的问题了!
----- ----------
2 drop
1 cat
1 hat
3 gg
2 haveElapsed: 00:00:00.00
18:51:30 SQL> select * from test t1 where exists (select id,min(rowid) from test t2 group by id havi
ng min(rowid) = t1.rowid);ID NAME
----- ----------
2 drop
1 cat
3 ggElapsed: 00:00:00.00
from table
group by column;即可
231
123
888
888
888
333
333
222
222
222
111(这个集合是我通过一系列操作select出来的,包括了order by),然后我想显示出唯一的,group by应该也是排序的.我想得到的结果是子查询的顺序,如下(不想它排序,但又想它唯一):
231
123
888
333
222
111
select * from test t1 where rowid in (select min(rowid) from test t2 group by id);
后面的方法因为是full scan by rowid,所以速度有质的飞跃
这个subquery得出来的结果是按照id排序的,但是不影响最后的结果,因为外面用的是in