We help IT Professionals succeed at work.

SQL SELECT the last ten entries of each items

JoeUS
JoeUS asked
on
288 Views
Last Modified: 2012-08-13
Hi experts
I need to select the TOP (10) of each DISTINCT items from a table (not the DISTINCT TOP (10) ).

Select DISTINCT  sFName from logTable
Where  logTableID = (select  top 10 ( logTableID) from logTable)

Basically I want the last ten entries of each items
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you please post some sample data, this one is confusing...

Author

Commented:
Here is the sample.
I would like to see each of the (three) names 10 times).
The first ten records are Denise's
The second  ten records are Kate's
The third ten records  are Joe's

the result should be ordered by sFName desc,  logTableID desc

hope it helps
logTableID	sFName
1	Joe
2	Joe
3	Joe
4	Joe
5	Denise
6	Kate
7	Kate
8	Joe
9	Kate
10	Joe
11	Joe
12	Joe
13	Kate
14	Kate
15	Denise
16	Kate
17	Kate
18	Kate
19	Denise
20	Joe
21	Kate
22	Kate
23	Kate
24	Denise
25	Kate
26	Kate
27	Denise
28	Kate
29	Denise
30	Kate
31	Kate
32	Denise
33	Denise
34	Joe
35	Denise
36	Denise
37	Kate
38	Denise
39	Denise
40	Joe
41	Kate
42	Joe
43	Denise
44	Kate
45	Denise
46	Joe
47	Denise
48	Denise
49	Joe
50	Kate
51	Kate
52	Denise

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try this...if it is not whay you are looking for you should post some data and show what you want...

select * from (
Select DISTINCT sFName, RANK() OVER (PARTITION BY sFName ????) AS RANK
from logTable
Where  logTableID = (select  top 10 ( logTableID) from logTable)
) x where rank <=10
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
try this

select logTableID, sFName, RANK() OVER (PARTITION BY sFName logTableID) AS RANK
from logTable
where rank<=10

or

select * from (
select logTableID, sFName, RANK() OVER (PARTITION BY sFName logTableID) AS RANK
from logTable
) x where rank<=10
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
try this

select sFName,logTableID from (
select sFName,logTableID,row_number() over (partition by sFName order by logTableID desc) as rn
  from YourTable) as t1
 where rn <= 10
Data Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
it gives me unhandled win32 exception SqlWb.exe [4036] error
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
"it gives me unhandled win32 exception SqlWb.exe [4036] error" what is that "it"?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
did you try 25799833?
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
How are you executing the query?

Author

Commented:
I am calling the query from a query window in management studio after opening the table.
No I did not try 25799833, but I did give a shot to er7649ju and it seems to run just fine.
Should I try to run it from an SP?
I will anyway:)

Author

Commented:
Yep, it is a whole new ballgame

Author

Commented:
Now, you must know the answer already.  
How is it going to scale when I hit a few millions of records?

Author

Commented:
Thanks for the great help!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.