locked
help me with store procedure RRS feed

  • Question

  • Hi

    Display based on customerid display max of item they purchased on a order display only number

    like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.

    using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter

    Any help is appreated.

    cid        oid       items

    1           1            10

    1           2          12

    1          3           3

    1        4               4

    so if we enter 1 as custid it got to give us 12 as the result

    Thanks

    newsql


    • Edited by knewsql Thursday, November 12, 2015 6:03 PM
    Thursday, November 12, 2015 5:37 PM

Answers

  • I see you edited your original post to include some example data.

    Going forward please note two things:

    1) We're not here to do your homework for you
    2) Please provide DDL and example data as I showed you.

    DECLARE @table TABLE (cid INT, oid INT, items INT) INSERT INTO @table (cid, oid, items) VALUES (1, 1, 10), (1, 2, 12), (1, 3, 3), (1, 4, 4)

    DECLARE @customerID INT = 1 ;WITH seq AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY items) AS seq FROM @table ) SELECT * FROM seq WHERE seq = 1 AND customerID = @customerID

    Here's an example on how to achieve what you requested, you're on your own to turn it into a procedure.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb


    Friday, November 13, 2015 2:00 PM
  • Thank you for all the answers the last on eworked for me.

    Thanks

    • Marked as answer by knewsql Monday, November 16, 2015 1:36 PM
    Friday, November 13, 2015 4:54 PM

All replies

  • Please provide DDL and example data, as well as an expected output and we will be able to help you.

    Here's an example:

    DECLARE @table TABLE (col1 INT, col2 DATE, col3 NVARCHAR(10))
    INSERT INTO @table (col1, col2, col3) VALUES
    (1, CURRENT_TIMESTAMP, 'xyz'),
    (1, CURRENT_TIMESTAMP-1, 'def')


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Thursday, November 12, 2015 5:57 PM
  • select top (1) * from DataTable where cId = @CustomerId ORDER BY Items DESC

    Or

    create procedure FindMaxItemsInOrderByCustomer

    (@CustomerId int,

    @Items int OUTPUT)

    as 

    begin

    set nocount on;

    ;with cte as (select *, row_number() over (partition by cId order by Items DESC) as Rn from DataTable

    where cId = @CustomerId)

    select @Items = items from cte where Rn = 1

    end

    -----------------------

    from the top of my head


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, November 12, 2015 6:15 PM
  • declare @t as table(cid int,oid int,items int)
    insert into @t 
    values(1,1,10),(1,2,12),(1,3,3),(1,4,4);
    select * from @t 
    declare @custid int = 1;
    
    select max(items) from @t where cid = @custid group by cid
    
    RESULT:
    ------
    12


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Friday, November 13, 2015 5:24 AM
  • I see you edited your original post to include some example data.

    Going forward please note two things:

    1) We're not here to do your homework for you
    2) Please provide DDL and example data as I showed you.

    DECLARE @table TABLE (cid INT, oid INT, items INT) INSERT INTO @table (cid, oid, items) VALUES (1, 1, 10), (1, 2, 12), (1, 3, 3), (1, 4, 4)

    DECLARE @customerID INT = 1 ;WITH seq AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY items) AS seq FROM @table ) SELECT * FROM seq WHERE seq = 1 AND customerID = @customerID

    Here's an example on how to achieve what you requested, you're on your own to turn it into a procedure.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb


    Friday, November 13, 2015 2:00 PM
  • Thank you for all the answers the last on eworked for me.

    Thanks

    • Marked as answer by knewsql Monday, November 16, 2015 1:36 PM
    Friday, November 13, 2015 4:54 PM
  • That's great to hear. Can you mark the appropriate post so other people can find it easily? Thanks!

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Friday, November 13, 2015 5:01 PM