Help to write a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help to write a query

Author

Topic

sp3010
Starting Member

8 Posts

Posted - 2007-12-27 : 00:56:37

Table having 3 col name wono,assetid,date.

wono assetid date
25001 10001 19-Jan-07
25002 10002 20-Jan-07
25003 10003 20-Jan-07
25004 10002 21-Jan-07
25005 10003 22-Feb-07
25006 10002 22-Feb-07

I want that query will sort the record on count of assetid & show resutls as following

25002 10002 Date
25004 10002 do
25006 10002 do
25003 10003 do
25005 10003 do
25001 10001 do

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 01:12:59

SELECT t1.wono,t1.assetid,t1.date
FROM Table t1
INNER JOIN (SELECT assetid,COUNT(*) AS 'RecCount'
FROM Table
GROUP BY assetid) t2
ON t2.assetid = t1.assetid
ORDER BY t2.RecCount DESC,t1.wono ASC