Need help in grouping ....

Need help in grouping ....

• bluedesert1001

  ok you have too many fields in your group by clause.
  
  take out CarReviews.Date and CarReviews.Names fields
  
  What you need to do is create this query only returning the rows you want to group by.
  
  The create another query that joins this query and provides the other fields you need to see.
  
  
  
  

• Dato

  Yessssssssssssssssssssssss..... I got It...
  
  Thanks....
  this is the final query...
  
  SELECT    CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, CarReviews.Company, CarReviews.Name AS Reviewer, CarReviews.Id
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  WHERE CarReviews.Date In (SELECT     MAX(CarReviews.Date) AS Date
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id AND CarMake.Id = CarModel.MakeId
  GROUP BY CarMake.MakeName, CarModel.ModelName)
  
  
  the bold SQL will return the Latest dates of every car.... rest of the statement will return all the records....
  
  
  Your help 'Lee_Dale' really helped me solving the problem....
  
  
  Thanks & Goodbye
  Erphan Rajput.
  

• sekhar_16

  Ok take that field out of the group by clause
  
  
  

• Mattyv

  You mean first query will return this....
  
  MINI Cooper S
  Audi A4
  
  and second query will get the rest of the fields...
  
  
  ....
  
  one thing ... how should i join these two queries....
  
  
  THanks....
  
  
  Erphan Rajput
  

• Jereck

  Thanks for your reply....
  
  I have tried the modified query ...
  
  Server: Msg 144, Level 15, State 1, Line 6
  Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
  
  
  this error occurs when i try to run this command...
  
  the error line is highlighted... i think the error is in MAX(CarReviews.Id)... in Groupby clause...
  
  
  
  
  SELECT     CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName,
                        CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, MAX(CarReviews.Id) AS Id
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  GROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, MAX(CarReviews.Id), CarReviews.Name,
                        CarReviews.Company, CarReviews.Date
  ORDER BY CarReviews.Date DESC
  
  
  
  
  
  Thanks,
  Erphan Rajput
  

• Fredrik Wassberg

  The first query could return the MAX(Date) field and you could join the date fields from both queries
  
  e.g.
  
  SELECT     MAX(CarReviews.Date) AS Date, CarMake.MakeName, CarModel.ModelName,
                        CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  GROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image
  
  
  
  
  

• pxcosis

  What you need to do is put MAX() around the CarReviews.Id field in the SELECT and the GROUP BY part e.g.
  
  SELECT     CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName,
                        CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, MAX(CarReviews.Id) AS Id
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  GROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, MAX(CarReviews.Id), CarReviews.Name,
                        CarReviews.Company, CarReviews.Date
  ORDER BY CarReviews.Date DESC
  
  
  Cant test this so not sure it will work but the basic problem is you are grouping by too many fields so it is returning a record for each entry into the table rather than the last entry.
  
  
  

• Larry G.

  I posted several examples on how to do this using the new Yukon features and traditional SQL way. The method you have right now is the standard SQL way. I am posting those below.
  
  
  Answer Re: Get latest purchase of each vender
  
  There are many ways to do this.
  
  -- any version of SQL Server / ANSI SQL
  select b1.VInvoiceID, b1.Date, b1.VenderID
  from BuyingTable as b1
  where b1.Date = (select max(b2.Date)
  from BuyingTable as b2
  where b2.VenderID = b1.VenderID);
  
  -- SQL Server 2005 specific/ANSI SQL
  select b2.VInvoiceID, b2.Date, b2.VenderID
  from (
  select b1.VInvoiceID, b1.Date, b1.VenderID, ROW_NUMBER() OVER(PARTITION BY b1.VenderID ORDER BY b1.Date DESC) as OrderSeq
  from BuyingTable as b1
  ) as b2
  where b2.OrderSeq = 1;
  
  -- Same as above using CTE instead of derived table
  with b2
  as
  (
  select b1.VInvoiceID, b1.Date, b1.VenderID, ROW_NUMBER() OVER(PARTITION BY b1.VenderID ORDER BY b1.Date DESC) as OrderSeq
  from BuyingTable as b1
  ) as b2
  select b2.VInvoiceID, b2.Date, b2.VenderID
  from b2
  where b2.OrderSeq = 1;
  
  -- SQL Server 2005 specific. Same as query #1
  select b2.VInvoiceID, b2.Date, b2.VenderID
  from (select b.VenderID, max(b.Date) as Latest
  from BuyingTable as b
  group by b.VenderID) as b1
  cross apply (select b2.*
  from BuyingTable as b2
  where b2.VenderId = b1.VenderId
  and b2.Date = b1.Latest) as b2;
  
  Answer Re: Doing a SELECT on a different field than the field in the GROUP BY
  
  I posted few queries that show how to do this for a similar problem. Please see the thread titled "Get latest purchase of each vender". Here are few queries:
  
  -- Works from SQL70 onwards:
  select t1.*
  from tblSaleAddresses as t1
  where t1.AddressId = (select top 1 t2.AddressId
  from tblSaleAddresses as t2
  where t2.SaleID = t1.SaleID
  and t2.DateEffective <= CURRENT_TIMESTAMP
  order by t2.DateEffective DESC)
  and t1.DateEffective <= CURRENT_TIMESTAMP;
  
  -- SQL2005 specific:
  select t2.*
  from (
  select t1.*
  , ROW_NUMBER() OVER(PARTITION BY t1.SaleID
  ORDER BY t1.DateEffective DESC
  , t1.AddressID DESC) as AddrSeq
  from tblSaleAddresses as t1
  where t1.DateEffective <= CURRENT_TIMESTAMP
  ) as t2
  where t2.AddrSeq = 1;
  
  -- ANSI SQL query
  select t1.*
  from tblSaleAddresses as t1
  join (
  -- I concatenate date & id as binary data and take MAX on that first
  -- then you can parse the address id of the max value
  -- this works for positive values of AddressId and Date
  -- you can character values that sorts correctly also.
  select cast(right(max(cast(convert(char(8), t2.DateEffective, 112) as binary(8))
  + cast(t2.AddressID as binary(4))), 4) as int)
  from tblSaleAddresses as t2
  where t2.DateEffective <= CURRENT_TIMESTAMP
  group by t2.SaleID
  ) as t3(AddressId)
  on t3.AddressId = t1.AddressId;
  
  

• Maturin

  The first query could return the MAX(Date) field and you could join the date fields from both queries
  
  
  

• PProg

  I have took that field out of TSQL but results are same...
  
  in the following query i have removed some fields... even now i am getting the repeation....
  
  
  SELECT     MaX(CarReviews.Date) AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName,
                        CarModel.YearName
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  GROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarReviews.Name, CarReviews.Company, 
                        CarReviews.Date
  
  DATE          Company         Author           Car Name w/ Model and Year
  2005-10-01 MotoFlare.com  Erphan Rajput Audi A4 (2005.5) 2005
  2005-10-01 MotoFlare.com  Jim Flammang Audi A4 (2005.5) 2005
  2005-10-03 LATimes.com    Dan Neil MINI Cooper S 2005
  2005-10-03 Star-Telegram  G. Chambers Williams III MINI Cooper S 2005
  2005-10-03 MotoFlare.com  Jim Flammang MINI Cooper S 2005
  2005-10-03 MotoFlare.com  Joe Wiesenfelder MINI Cooper S 2005
  
  
  
  Thanks....
  

• SohanKamat

  glad to be of service
  
  
  

• bpfh

  Ok, i got your idea....
  
  now
  
  this query returns 2 cars with latest review's date...
  
  SELECT     MAX(CarReviews.Date) AS Date, CarMake.MakeName, CarModel.ModelName
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id AND CarMake.Id = CarModel.MakeId
  GROUP BY CarMake.MakeName, CarModel.ModelName
  
  RESULTS:
  ---------
  2005-10-01 16:19:25.357   Audi A4 (2005.5)
  2005-10-03 17:12:25.373   MINI Cooper S
  
  
  SECOND Query:
  --------------
  SELECT    CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarReviews.*
  FROM         CarReviews INNER JOIN
                        CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN
                        CarModel ON CarReviews.ModelId = CarModel.Id
  
  This query returns all the records....
  
  
  
  Now tell me how should i join these two queries....
  
  
  
  Thanks,
  Erphan Rajput...
  
  
  
  
  
• Need help in grouping ....