Solved

SQL - Top six per unit

Posted on 2008-06-20
11
295 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:josephdaviskcrm
  • 4
  • 4
  • 3
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21831063
what is the primary key of the field?
also, what is the "order by" for the TOP ?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21831066
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
 

Author Comment

by:josephdaviskcrm
ID: 21831175
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21831214
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
 

Author Comment

by:josephdaviskcrm
ID: 21831247
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 60

Expert Comment

by:chapmandew
ID: 21831290
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21831292
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
 

Author Comment

by:josephdaviskcrm
ID: 21831558
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 250 total points
ID: 21831582
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21831600
>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
 

Author Closing Comment

by:josephdaviskcrm
ID: 31469143
That second version worked perfectly.  Thanks to both of you.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now