SQL Server Performance

Getting only top 2 rows of information for each item in a table in MSSQL 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by kky1971, May 18, 2009.

  1. kky1971 New Member

    I am trying to get top 2 rows of information from each item in a table. The table have many items and locations, the unqiue row is item + loc. I have tried TOP n, set rowcount but not the result I am looking looking for. The idea is getting the top 2 loc with the minimum qty for each item.
    item loc Qty
    ITEMA LOCA 2
    ITEMA LOCB 4
    ITEMA LOCC 10
    ITEMB LOCD 20
    ITEMB LOCE 14
    ITEMB LOCF 10
    ITEMC LOCG 4
    ITEMC LOCH 16
    ITEMC LOCI 12
    Expected result
    item loc Qty
    ITEMA LOCA 2
    ITEMA LOCB 4
    ITEMB LOCF 10
    ITEMB LOCE 14
    ITEMC LOCG 4
    ITEMC LOCI 12
    Is there a way to write this in a select statement without going through store procedure
  2. dhamu294 New Member

    hi, use cursor and fetch the item one by one and get top 2 and store it in temp table
  3. FrankKalis Moderator

    [quote user="kky1971"]
    I am trying to get top 2 rows of information from each item in a table. The table have many items and locations, the unqiue row is item + loc. I have tried TOP n, set rowcount but not the result I am looking looking for. The idea is getting the top 2 loc with the minimum qty for each item.
    item loc Qty
    ITEMA LOCA 2
    ITEMA LOCB 4
    ITEMA LOCC 10
    ITEMB LOCD 20
    ITEMB LOCE 14
    ITEMB LOCF 10
    ITEMC LOCG 4
    ITEMC LOCH 16
    ITEMC LOCI 12
    Expected result
    item loc Qty
    ITEMA LOCA 2
    ITEMA LOCB 4
    ITEMB LOCF 10
    ITEMB LOCE 14
    ITEMC LOCG 4
    ITEMC LOCI 12
    Is there a way to write this in a select statement without going through store procedure
    [/quote]
    No need for a cursor...
    ;WITH myCte (item, loc, qty, rn)
    AS
    (SELECT item, loc, qty , RANK() OVER (PARTITION BY item ORDER BY qty) AS rn
    FROM
    MyTable
    )
    SELECT
    myCte.[item],
    myCte.loc,
    myCte.qty
    FROM
    myCte
    WHERE
    rn <= 2;
    Why don't you want to use a stored procedure?
  4. Adriaan New Member

    Use a correlated subquery.
    You need the top 2 locations per item code, ordering on the qty (ascending):
    SELECT * FROM dbo.tblItem i
    WHERE i.loc IN
    (SELECT TOP 2 x.loc FROM dbo.tblItem x
    WHERE x.item = i.item
    ORDER BY x.Qty ASC)
  5. dhamu294 New Member

    Hi Frank
    SELECT item, loc, qty , RANK() OVER (PARTITION BY item ORDER BY qty) AS rn
    FROM tablename
    i tryed using this query sql server 2000 , i am getting error in rank() and over
    can you explain waht all this
  6. Adriaan New Member

    Frank's version will work only in SQL 2005 and SQL 2008. For 2000 or earlier, use my syntax.
  7. Madhivanan Moderator

  8. FrankKalis Moderator

    Sorry, since you've posted this in a SQL Server 2005 forum, I thought that you are using that version.

Share This Page