To display top 2 records for all sales person

Question

To display top 2 records for all sales person

venkat-216866

SSC Veteran

Points: 257
More actions
November 16, 2005 at 5:23 am

#88495

In Salesdetails table i have value like
----------------------------------------
Keyid                       sale    Saledate
                            person
----------------------------------------
30611963529604524974 3907 20050720
0611962429603545866 3907 20050720
0611923319603904967 3907 20050720
0611923729606127347 3907 20050720
0611963429604886272 3907 20050725
0611921173804635511 3907 20050802
0611923829606089945 3907 20050802
0611950919600056169 3907 20050726
0611972229600054644 4826 20050728
0611923329604908541 4826 20050728
0611961129603305188 4826 20050728
0611956519603034236 4826 20050726
0611958329603444219 4826 20050727
0611963319603313431 4826 20050727
0612023729606198240 5353 20050808
0612023529604588424 5353 20050815
0612081212011631717 5353 20050815
0612082251019221452 5353 20050815
0612041629604000478 5353 20050811
0612023529604436955 5353 20050808
0612050929600257270 5353 20050808
0612079106027312535 5353 20050803
0612112029600090291 5353 20050812
0612119429604104443 5353 20050812
0612122719603414346 5353 20050812
-----------------------------------
From the above table i need top 2(any records from all saled person like)
----------------------------------------
Keyid                   sale    Saledate
                        person
----------------------------------------
30611963529604524974 3907 20050720
0611962429603545866 3907 20050720
0611958329603444219 4826 20050727
0611963319603313431 4826 20050727
0612119429604104443 5353 20050812
0612122719603414346 5353 20050812
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

What is 'sale person' here? Is it the ID of the salesperson or the amount sold?

Care to explain a bit on why you need 2 of each grouping? Is it because of something done earlier, or something that need to be done afterwards? Reason for asking is that most problems have several solutions - when the purpose is known, it's also possible to see alternative solutions. (that may not need the asked-for specific problem to be solved at all)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

See if this helps

USE Northwind

SELECT t1.CustomerID , t1.OrderDate

FROM Orders t1

WHERE

(SELECT COUNT(*)

FROM Orders

WHERE OrderDate<=t1.OrderDate AND CustomerID = t1.CustomerID) <=2

ORDER BY t1.CustomerID, t1.OrderDate DESC

or

SELECT t1.CustomerID, t1.OrderDate

FROM Orders t1

WHERE t1.OrderDate IN

(SELECT TOP 2 t2.OrderDate

FROM Orders t2

WHERE t2.CustomerID = t1.CustomerID

ORDER BY t2.OrderDate DESC)

ORDER BY t1.CustomerID, t1.OrderDate DESC

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

venkat-216866 SSC Veteran Points: 257 More actions · Answer 3

Thanks for your response

Just for Analysis purpose, Some time sales manager wants to check some data (for quality). So SM just wants any two (or top 2) records.

venkat-216866 SSC Veteran Points: 257 More actions · Answer 4

And i also tried using this

select top 2* from sales M where keyid IN

(select top 2 keyid from sales M1

where M1.keyid=M.keyid and m.saleperson =m1.saleperson )

But the above query display all the records

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Seems like a very typical manager request then... I only wonder how the manager thinks it would be possible to check for quality when the data retrieved is unknown..? (since it was 'any' two rows)

In any case, Franks solution would probably make your manager happy.

/Kenneth

venkat-216866 SSC Veteran Points: 257 More actions · Answer 6

Hi Kenneth

In the sales table we have nearly 5,00,000 lac records. It's impossible to check all the records. So that he just pick any two records for quality checking.

Franks solution also gave all the records... Please help me to sort out this

thanks

venkat-216866 SSC Veteran Points: 257 More actions · Answer 7

Hi all

i got it.. this is the query...

select * from sales M where keyid IN

(select top 2 keyid from sales M1

where m.saleperson =m1.saleperson )

thanks all