How to make the order by date fine enough in SQL

hi,

I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.

Thanks very much.

Then you're trying to do a select order by datedesc, and group by date, within each group you wanna order by timeasc, right? If so you’re trying to do sorting within grouped records. In SQL2005 you can useRANK Functions, for example:

SELECT*,RANK()OVER(PARTITIONBYCONVERT(VARCHAR,CDATE,102)

ORDERBYCONVERT(VARCHAR,CDATE,108))AS RANK

FROM testP

ORDERBYCONVERT(VARCHAR,CDATE,102)DESC

|||Thanks!

How to make a random order query ?

If we use: select * from .... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?Hi,

FYI:http://www.datawebcontrols.com/faqs/Data/ReturningDataInRandomOrder.shtml

Regards,

How to list all tables ordered by their size within a SQL Server 2005 database?

Hi, all,

Would please any expert here give me any guidance and advices for how to list all tables sorted by their size within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi,

A similar kind of query I've seen on a site. In the example given by the author of the query, he usedDynamic Management Views. You can find the original query from here http://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx

As per your requirement, I have made some changes to the query. Here it is

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )
AS(SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
,casewhen i.index_id in( 0, 1 )then p.row_count else 0 end
FROMsys.dm_db_partition_stats p
INNERJOINsys.objectsas o ON o.object_id= p.object_id
INNERJOINsys.schemasas s ON s.schema_id = o.schema_id
LEFTOUTERJOINsys.indexesas i on i.object_id= p.object_idand i.index_id = p.index_id
WHERE o.type_desc ='USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name
, t.table_name
,sum(t.used)as used_in_kb
,sum(t.reserved)as reserved_in_kb
,sum(t.tbl_rows)as rows

FROM table_space_usage as t

GROUPBY t.schema_name , t.table_name

ORDERBY used_in_kb desc

Hope it will help you

|||

Hi, Neeraj,

Thanks a lot for your very helpful guidance.

With best regards,

Yours sincerely,