TOP FUNCTION

Question

Post reply

TOP FUNCTION

Viewing 6 posts - 16 through 20 (of 20 total)

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

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 1

Ananth-431739

Ten Centuries

Points: 1185

December 18, 2007 at 7:37 am

#761021

thanks peter

toniupstny Hall of Fame Points: 3986 More actions · Answer 2

Hi.. try this.... change the TOP 3 clause to be TOP ( the number of invoices for each account) you want. It worked for what I tried.

DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

SET DATEFORMAT DMY

INSERT@Sample

SELECT'inv1', 'acc1', '01/01/2007' UNION ALL

SELECT'inv2', 'acc1', '01/02/2007' UNION ALL

SELECT'inv3', 'acc1', '01/03/2007' UNION ALL

SELECT'inv4', 'acc1', '01/04/2008' UNION ALL

SELECT'inv5', 'acc2', '11/05/2007' UNION ALL

SELECT'inv6', 'acc2', '12/06/2007' UNION ALL

SELECT'inv7', 'acc2', '01/07/2008' UNION ALL

SELECT'inv8', 'acc3', '13/08/2007'

select s1.account, s1.date

from @sample s1

where s1.date in (select TOP 3 s2.date from @sample s2

where s1.account = s2.account

order by s2.date desc)

group by s1.account, s1.date

order by s1.account, s1.date desc

Let me know if it does the trick

Toni (sorry I do not know how to make a code window)

pendotakiran SSC Rookie Points: 40 More actions · Answer 3

That means You want

ACC1,Acc1

for the above result (Using Sql Server )

Select Top 2 INV,Acc,Date from tblTable Order by Acc

or

Acc1,Acc1,Acc2,Acc2.

Select INV,Acc,Date from tblTable where Acc in ( Select Distinct Top 2 Acc from tblTable Order By Acc)

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 4

select s1.account, s1.date

from @sample s1

where s1.date in (select TOP 3 s2.date from @sample s2

where s1.account = s2.account

order by s2.date desc)

group by s1.account, s1.date

order by s1.account, s1.date desc

I think this query will return the TOP 3 rows for that account. But if the Invoice date is same for all the invoices for that account, then the outer query will return all the accounts, not TOP 3 invoice for that account.

toniupstny Hall of Fame Points: 3986 More actions · Answer 5

Ananth, first you could add the TOP n clause to the outer select if you needed to;however, based on the specs I don't believe returning all the invoices is wrong if they want the latest ones and it is based on the date (which you propose could be all the same).

Help me to understand your point and how you would correct it

Toni

toniupstny Hall of Fame Points: 3986 More actions · Answer 6

Ok... I tested with all dates the same and it returns one row per account due to the Group by clause.

So I am still wondering what you saw Ananth?

Toni