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)  how to do this!

Author  Topic  ajay Starting Member 34 Posts Posted - 2001-08-24 : 01:10:10 Table structure is like this filename varchar(30) category varchar(3) description varchar(2000) Naming convention if field filename is like 'HTMCP then 10digit variable then yymmdd then xyz.htm for e.g filename entered today would be 'HTMCP1234567890yymmddxyz.htm yymmdd id today's date. category can have value DSD,PUB,MTL DSD will be the date in the form of varchar dd/mm/yy PUB will be the publication name MTL will be the headline. So for single filename there will be three records. For one file name: HTMCP1234567890yymmddxyz.htm MTL this is the headline HTMCP1234567890yymmddxyz.htm PUB Newyork times HTMCP1234567890yymmddxyz.htm DSD dd/mm/yy The yymmdd in the filename will be equal to the dd/mm/yy in DSD category. Now this table contains millions of records like this; Now i want to retreive latest 50 distinct filename from this table. I have written this query for ORACLE database, because part of my project involves connectivity with oracle database. select distinct filename from table where rownum < 50 order by to_date(substr(filename,16,6),'yymmdd') desc or How is this possible in SQL SERVER 7.0. 16,6 bcos the date in the filename starts from 16th position and ends at 21st position. After I run the query it does not gives me the latest records. Thanks in Advance ajay