Post reply

getting the top 1000 records

  • October 1, 2004 at 4:36 am

    #85608

    Hi There,

    I am new to MS SQL queries, so please excuse if this is a simple thing to do.

    I am stating my problem below:

    I am working on MSSQL 2000.

    I have a table consisting of the following columns:

    telephoneno

    requestid

    requestdetails.

    One telephone number can make many requests.

    What I want is the telephone numbers that have made the maximum requests (in descending order till a count of 1000) along with the count of requests that the telephone number has made.

    Can anyone help me?

    Its rather urgent. Accept my thanks in advance.

    Regards

    Supriy

  • October 1, 2004 at 4:58 am

    #524997

    SELECT TOP 1000 WITH TIES DT.telephoneid , DT.Requests

    FROM (

    SELECT telephoneid,count(*) AS Requests

    FROM tbl

    GROUP BY telephoneid

    ) AS DT.

    ORDER BY DT.Requests DESC

     

    This will return the TOP 1000 but also take into account situations where record 1000 and record 1001 have the same number of requests.

    LinkedIn Profile

  • October 1, 2004 at 5:35 am

    #525003

    I am very sorry, but when i replace the DT with my table name (DT is tablename isn't it?), it gives me message column prefix is invalid.

    Also what is the DT. after the Group By statement.

    I apologise to bother and this may be very elementry but I cannot get thru.

    Again thanks in advance.

    Supriy

  • October 1, 2004 at 5:39 am

    #525005

    Remove the dot after the AS DT.

    DT is to name the derived table used in the query.

    You can then make reference to it by using this alias.

     

    Kindest Regards,

    Andre Vigneau MCSE, MCDBA

  • October 1, 2004 at 7:01 am

    #525012

    Thanks a lot!!!

    It worked.

    Kind Regards

    Supriy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply