SQL - Top six per unit

SELECT     TOP (6) StoreID, Location, OERDate AS Date, Rating, Score
FROM         dbo.*********
WHERE     (StoreID IN ('000001', '000005')) AND (OERDate BETWEEN '1/1/2003' AND '1/1/2008')
---------------------------------------------------
The above SQL gives the top six for the whole set.  I want to get the top six for each StoreID, so there would potentially be 12 records returned.
josephdaviskcrmAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in case the database compatibility  is set to 80 (=sql 2000) and could not be changed:
SELECT t.StoreID, t.Location, t.OERDate AS Date, t.Rating, t.Score
  FROM dbo.********* t
 WHERE t.OERDate IN ( Select TOP 6 i.OERDate
                       FROM   dbo.********* i
                      WHERE i.StoreID = t.StoreID
                        AND i.OERDate BETWEEN '1/1/2003' AND '1/1/2008')
                      ORDER BY i.OERDate DESC
                    )
WHERE  t.StoreID IN ('000001', '000005')

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the primary key of the field?
also, what is the "order by" for the TOP ?
0
 
chapmandewCommented:
try this:

select * from
(
SELECT     StoreID, Location, OERDate AS Date, Rating, Score, ranking = dense_rank() over(partition by storeid order by rating asc)
FROM         dbo.tablename
WHERE     (StoreID IN ('000001', '000005')) AND (OERDate BETWEEN '1/1/2003' AND '1/1/2008')
) a
where ranking <= 6
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
josephdaviskcrmAuthor Commented:
angelIII:
the database object is  structure so there really isn't a primary key defined I don't think.  I guess if there had to be a primary key it would be storeID and Date together.  Also, sorry I forgot to add the order by clause, thats important.  I meant to add ORDER BY date DESC.  So that it selects the 6 items with the most recent dates from each unit.

chapmandew:
The dense_rank() over(partition by storeid order by rating asc) statement threw an error.  SQL Server said that it didn't support the OVER statement.  Also, can you explain what that code is supposed to do?  I'm not familiar with those commands.
0
 
chapmandewCommented:
Are you sure that you're using 2005 and that your db compatability level is set to 90?

What it does is for each storeid that it partitions by (groups by) it orders them sequentially based on the order of the rating.  It's new functionality in SQL 2005.
0
 
josephdaviskcrmAuthor Commented:
Yeah I'm sure its 2005.  I don't know about the compatability level.  I'm working in a large organization on their database, so I'm sure that it should be all set up right.

Is there an easier way to do it thats not trying to use this new functionality?
0
 
chapmandewCommented:
Well, I just tried it on a 2005 instance with a compat. level of 80 and it worked fine.  

What do you get when you run this?

SELECT SERVERPROPERTY('ProductVersion')
0
 
josephdaviskcrmAuthor Commented:
chapmandew: 9.00.3054.00

angelIII: That code is throwing me an error too.  It says that it can't be parsed.

I'm just going to go through and grab the data one store at a time.  Thanks guys.
0
 
chapmandewConnect With a Mentor Commented:
Try this variation:

SELECT t.StoreID, t.Location, t.OERDate AS Date, t.Rating, t.Score
  FROM dbo.tabled t
 WHERE t.OERDate IN ( Select TOP 6 i.OERDate
                       FROM   dbo.tabled i
                      WHERE i.StoreID = t.StoreID
                        AND i.OERDate BETWEEN '1/1/2003' AND '1/1/2008'
                      ORDER BY i.OERDate DESC
                    )
AND  t.StoreID IN ('000001', '000005')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>angelIII: That code is throwing me an error too.  It says that it can't be parsed.
question: are you using the query designer?
answer: don't. it's too limited
0
 
josephdaviskcrmAuthor Commented:
That second version worked perfectly.  Thanks to both of you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.