Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Saturday, February 23, 2013 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:13 PM
Points: 5, Visits: 48
i have table with following structure.
SELECT SaleID, vender_inv, Code, cDate
FROM SaleMain

i want to fetch last two invoice against each code.
where saleid is auto incremented and cdate as well to get last invoice.

help will be really appreciated.
Post #1423396
Posted Sunday, February 24, 2013 3:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 7,125, Visits: 13,173
You might want to look into the CROSS APPLY syntax (use it to get the Top 2 vender_inv ORDER BY SaleID DESC) together with a cte or subquery to return all distinct code values.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1423401
Posted Sunday, February 24, 2013 10:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
Something like this should do it:


--i have table with following structure.
--SELECT SaleID, vender_inv, Code, cDate
--FROM SaleMain

--i want to fetch last two invoice against each code.
--where saleid is auto incremented and cdate as well to get last --invoice.

select *
from
(
select SaleId, Vender_Inv, Code, cDate,
ROW_NUMBER() over (partition by Code order by cDate Desc) as RowNumber
FROM SaleMain
) A
where A.RowNumber < 3



Post #1423426
Posted Sunday, February 24, 2013 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:13 PM
Points: 5, Visits: 48
that's awesome laurie thanks a ton :)
Post #1423428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse