This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / HELP: I have a query here "select distinct username from users order by logindate". and I got error,HELP: I have a query here "select distinct username from users order by logindate". and I got error, but if I change is to "select distinct username,logindate from user" the result I got was not correct. how to fix it? THX
-shinning(dudu);
2005-6-8
{226}
(#2335478@0)
-
please help...I have already worked on it whole morning, no solution :(
-shinning(dudu);
2005-6-8
(#2335622@0)
-
group by order by max
-yuanzidan(原子弹);
2005-6-8
(#2335624@0)
-
Do you mean: "select distinct username,logindate from users group by order by max "?
-shinning(dudu);
2005-6-8
(#2335632@0)
-
hehe, 女孩子吧：)
select username from users group by username order by max(logindate) desc;
-yuanzidan(原子弹);
2005-6-8
(#2335640@0)
-
thank you, but it doesn't work ,the error:thank you, but it doesn't work ,the error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'users.logindate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
"
-shinning(dudu);
2005-6-8
{311}
(#2335692@0)
-
what's the db type? did you use max(logindate) in the order by?
-yuanzidan(原子弹);
2005-6-8
(#2335700@0)
-
logindate is"DATE" type
-shinning(dudu);
2005-6-8
(#2335711@0)
-
it can be MAX as well depends on your db type, have u ever tried?
-yuanzidan(原子弹);
2005-6-8
(#2335718@0)
-
I tried, error"[Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'users.logindate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. "
-shinning(dudu);
2005-6-8
(#2335732@0)
-
what's the db type?
-yuanzidan(原子弹);
2005-6-8
(#2335733@0)
-
username is "char", logindate is "Date"
-shinning(dudu);
2005-6-8
(#2335744@0)
-
I mean it's sql 2k or oracle .... forget it, as long as it works...
-yuanzidan(原子弹);
2005-6-8
(#2335753@0)
-
hehe...sorry,it's sql server 2000
-shinning(dudu);
2005-6-8
(#2335760@0)
-
Then I can tell you "select username from users group by username order by max(logindate) desc " is a valid sql statment in sql 2000 and return you correct results. I have sql 2k, oracle, db2... just tried.
-yuanzidan(原子弹);
2005-6-8
(#2335777@0)
-
It cat not ordered by date
-shinning(dudu);
2005-6-8
(#2335786@0)
-
SELECT USERNAME FROM TABLE GROUP BY USERNAME ORDER BY LOGINDATE
-23456789(大白呼);
2005-6-8
(#2335637@0)
-
error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'users.loginDate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. "
-shinning(dudu);
2005-6-8
(#2335696@0)
-
select distinct username from users
-hard20(hard20);
2005-6-8
(#2335706@0)
-
I did, but it needs "order by Date".........:(
-shinning(dudu);
2005-6-8
(#2335716@0)
-
or select username, Max(logindate) from users group by username order by Max(logindate)
-hard20(hard20);
2005-6-8
(#2335717@0)
-
thank you so much, it works.......:)
-shinning(dudu);
2005-6-8
(#2335739@0)
-
you r welcome. By using "Max" or "Min" depends on your requirement. Don't forget use "order by ..." clause. GL!
-hard20(hard20);
2005-6-8
(#2335759@0)
-
I added "order by", but it's not work.:(
-shinning(dudu);
2005-6-8
(#2335788@0)
-
sorry, I'm wrong, it's not sorted by Date. :(
-shinning(dudu);
2005-6-8
(#2335741@0)
-
在table "users"中要select unique username, 但是要 order by Date :(
-shinning(dudu);
2005-6-8
(#2335710@0)
-
SELECT USERNAME, MIN(LOGINDATE) FROM USERS GROUP BY USERNAME ORDER BY LOGINDATE
-23456789(大白呼);
2005-6-8
(#2335715@0)
-
Same Error" [Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'users.logindate' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. "
-shinning(dudu);
2005-6-8
(#2335727@0)
-
SELECT USERNAME, MIN(LOGINDATE) FROM USERS GROUP BY USERNAME ORDER BY MIN(LOGINDATE)
-zxcvb(朝天椒);
2005-6-8
(#2335736@0)
-
SQL Server sucks
-23456789(大白呼);
2005-6-8
(#2335752@0)
-
yeah ...it really does......lol.......you are a nice guy .
-hard20(hard20);
2005-6-8
(#2335756@0)
-
Thanks for help, it still can not order by date, very strange, the most records order by date, only when I click page 5, it's mess up.the query is....Thanks for help, it still can not order by date, very strange, the most records order by date, only when I click page 5, it's mess up.the query is...." SELECT USERNAME, MIN(LOGINDATE) FROM USERS GROUP BY USERNAME ORDER BY MIN(LOGINDATE) "
-shinning(dudu);
2005-6-8
{241}
(#2335758@0)
-
try smashing your computer with a hammer
-23456789(大白呼);
2005-6-8
(#2335770@0)
-
Oh man.... try this one.
Select distinct USERNAME from select (USERNAME,LOGINDATE from USERS order by LOGINDATE ) .
-hard20(hard20);
2005-6-8
(#2335772@0)
-
LOL, 俺没有抄你的
-zxcvb(朝天椒);
2005-6-8
(#2335776@0)
-
Error: "[Microsoft][ODBC SQL Server Driver][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. "
-shinning(dudu);
2005-6-8
(#2335812@0)
-
select distinct USERNAME from
( select distinct USERNAME， LOGINDATE from table order by LOGINDATE)
-zxcvb(朝天椒);
2005-6-8
(#2335773@0)
-
the error I got" [Microsoft][ODBC SQL Server Driver][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. "
-shinning(dudu);
2005-6-8
(#2335795@0)
-
sorry "Select distinct USERNAME from (select USERNAME,LOGINDATE from USERS order by LOGINDATE ) "
-hard20(hard20);
2005-6-8
(#2335805@0)
-
select distinct USERNAME from ( select distinct USERNAME， LOGINDATE from table order by LOGINDATE) DERIVEDTBL#2335752
-zxcvb(朝天椒);
2005-6-8
{8}
(#2335816@0)
-
It is no need to use distinct twice.
-hard20(hard20);
2005-6-8
(#2335847@0)
-
you are right.
-zxcvb(朝天椒);
2005-6-8
(#2335855@0)
-
anyway, thanks a lot, you all nice guy.............................
-shinning(dudu);
2005-6-8
(#2335769@0)
-
Do u have "NULL" values for LOGINDATE column ?
SELECT USERNAME, MIN(LOGINDATE) FROM USERS where LOGINDATE is not null GROUP BY USERNAME ORDER BY LOGINDATE
-hard20(hard20);
2005-6-8
(#2335801@0)
-
非常感谢各位，问题已经解决了，真希望我能提高水平，以后也能帮别人。：） final solution: "select username, Min(loginDate) from users group by username order by Min(loginDate) "
-shinning(dudu);
2005-6-8
(#2335828@0)