You must Sign In to post a response.
  • Category: [Competition Entries]

    Retrieveing top 10 records from different companies .Write Sqlquery

    I have two tables Company and Transactions.Company is having fields CompanyID,
    Companyname.Transactions is having fields CompanyId,Transactiondatetime.Now I want top 10 transactions from different companies by using the above tables.
    Assume that there are 100 companies.How to write Sqlquery
  • #661772
    Please check below query,

    create table companytransaction
    (companyid int,
    transactiondatetime datetime)

    insert into company values
    (1,'AA'),
    (2,'BB'),
    (3,'CC')

    insert into companytransaction values
    (1,'01/03/2012'),
    (2,'02/03/2012'),
    (3,'03/03/2012')

    select distinct top 2
    a.companyid, a.companyname, b.transactiondatetime
    from company a, companytransaction b
    where a.companyid = b.companyid

  • #661814
    select Top 10 Transactions.date_time, Transactions.company_id,
    Company.company_id, Company.company_name from Tranasactions JOIN Company on
    Company.company_id = Transactions.company_id


    this is the query for the above scenario. its executing......

  • #661827
    Hi Suryanarayana ,

    You can follow any of the below two approachs..



    select Top 10 Transactions.date_time, Transactions.company_id, Company.company_name
    from Tranasactions T JOIN Company c on (c.company_id = t.company_id)

    or

    select Transactions.date_time, Transactions.company_id, Company.company_name
    from Tranasactions T JOIN Company c on (c.company_id = t.company_id)
    where (SELECT TOP 10 date_time FROM test1 ORDER BY date_time)





    Hope this will help you.
    Let me know if still problem.

    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring

  • #661831
    HI SURYNARAYAN,
    U CAN USE FOLLOWING QUERY,

    "select Top 10 Transactions.date_time, Transactions.company_id,
    Company.company_id, Company.company_name from Tranasactions JOIN Company on
    Company.company_id = Transactions.company_id"

  • #662435
    Hai Suryanarayana,

    You can use the Top clause of sql server to get the top 10 records. Make the join between the tables and retrieve the top 10 records.
    If you want to understand the Top clause, you can go through the below link where you can find the simple examples of using the Top clause:

    http://www.w3schools.com/sql/sql_top.asp

    As per the query which you have mentioned:

    SELECT TOP 10 TRANSACTIONS.DATE_TIME, TRANSACTIONS.COMPANY_ID,
    COMPANY.COMPANY_ID, COMPANY.COMPANY_NAME
    FROM TRANSACTIONS JOIN COMPANY ON
    COMPANY.COMPANY_ID = TRANSACTIONS.COMPANY_ID;

    Hope it will help you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #673365
    select Top 10 Transactions.date_time, Transactions.company_id,
    Company.company_id, Company.company_name from Tranasactions JOIN Company on
    Company.company_id = Transactions.company_id


    this is the query for the above scenario. its executing......


    Hope this will help u

    Regards
    Sandy


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.