MS SQL

MS SQL RSS

TOP seven data and grouping

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2007

Posts: 7

Reputation: Cano82 is an unknown quantity at this point

Solved Threads: 0

Cano82

Offline

Newbie Poster

TOP seven data and grouping

Dec 31st, 2007

The tables are joined. I want to display last 7 data with grouping their category

My tables:

Category: categoryId, categoryName
Order: orderId, catgoryId, product

•

Join Date: Jan 2008

Posts: 35

Reputation: mellamokb is an unknown quantity at this point

Solved Threads: 5

mellamokb

Offline

Light Poster

Re: TOP seven data and grouping

Jan 3rd, 2008

•

The tables are joined. I want to display last 7 data with grouping their category

I am going to guess this means you want the last 7 orders by category, in which case there would need to be some sort of date field, say Order.orderDate, to identify what "last" means:

(untested)

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT
  o1.[orderId],
  o1.[categoryId],
  c.[categoryName],
  o1.[product]
FROM
  [ORDER] o1
INNER JOIN
  [Category] c ON [Category].[categoryId] = o1.[categoryId]
WHERE
  o1.[orderId] IN (SELECT TOP 7 o2.[orderId] FROM [ORDER] o2 WHERE o2.[categoryId] = o1.[categoryId] ORDER BY o2.[OrderDate] DESC)
SELECT
  o1.[orderId],
  o1.[categoryId],
  c.[categoryName],
  o1.[product]
FROM
  [Order] o1
INNER JOIN
  [Category] c ON [Category].[categoryId] = o1.[categoryId]
WHERE
  o1.[orderId] IN (SELECT TOP 7 o2.[orderId] FROM [Order] o2 WHERE o2.[categoryId] = o1.[categoryId] ORDER BY o2.[OrderDate] DESC)