• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

SQL How Do I display a field without including it in the GroupBy clause?

How Do I display a field without including it in the GroupBy clause?  I'm binding two tables (MasterQuoteRec and History).  I need a field (NewValue) in the history table to display in the results.  I do not want to add it to the GroupBy function because it will group relationships and multiply the number of records by many factors.  The internal select clause already properly selects just the most recent record in the history table.  I just need the NewValue field to also display in the results of this query without changing my selection criteria.
select [Quote], [CustomerNumber], [Customer], [OrderReserveNumber], DateChanged, NewValue
from MasterQuoteRec m inner join 
	(SELECT QuoteID, max(DateChanged) AS DateChanged 
	 FROM History WHERE (Class = 'ProductionStatus' OR Class = 'AccountingStatus') 
	 GROUP BY QuoteID) hm on m.Quote = hm.QuoteID 
WHERE NewValue = 'In Shipping'

Open in new window

SQLError.jpg
0
dparkes
Asked:
dparkes
  • 5
  • 2
  • 2
  • +1
1 Solution
 
JestersGrindCommented:
One way to include a field without having it in the group by is to aggregate that field with MAX, MIN, SUM, etc.  

Greg

0
 
dparkesAuthor Commented:
The NewValue field is a text field containing the Status of the MasterQuoteRec.  Conforming to the reasoning of the SQL syntax people, I should write the code below.  But this gives me all the shipping records.  I don't want to know when all my orders were in Shipping.  I want to know where all my orders are now.  How might I write that?
select [Quote], [CustomerNumber], [Customer], [OrderReserveNumber], DateChanged, NewValue
from MasterQuoteRec m inner join 
	(SELECT QuoteID, NewValue, max(DateChanged) AS DateChanged 
	 FROM History WHERE (Class = 'ProductionStatus' OR Class = 'AccountingStatus') 
	 GROUP BY QuoteID, DateChanged, NewValue) hm on m.Quote = hm.QuoteID 
WHERE NewValue = 'In Shipping'

Open in new window

0
 
Christopher GordonSenior Developer AnalystCommented:
I use Common Table Expressions for these scenarios.
with Quote_Instances as
(
	select	*, ROW_NUMBER over (Partition By QuoteId Order By DateChanged DESC) as Instance_Counter
		
	from	History
	
	where  (Class = 'ProductionStatus' OR Class = 'AccountingStatus') 
)

, Quote_Instance_Recent as
(
	 select *
	 from	Quote_Instances
	 where	Instance_Counter = 1
)


select 
	m.[Quote]
,	m.[CustomerNumber]
,	m.[Customer]
,	m.[OrderReserveNumber]
,	m.DateChanged
,	quotes_recent.NewValue

from MasterQuoteRec m 

left outer join Quote_Instance_Recent quotes_recent on
	m.Quote = quotes_recent.QuoteId

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dparkesAuthor Commented:
You've put in considerable work on this solution.  I'm not really sure how all this works, but there are a few errors that perhaps you could help me with and then I'd be on the road.  Attached is the screen shot.
SQLError.jpg
0
 
dparkesAuthor Commented:
JesterGrind, I'd love to use an aggrevate function since that directly addresses the error message, but I can't think of one that will just give me the value of the most recent status record.  I thought something like "LAST" would be nice, but there is no such function.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read this article to see how to solve this kind of requests:
http://www.experts-exchange.com/A_3203.html
0
 
dparkesAuthor Commented:
Very enlightening, but does not address how to display the status with my selected records.  My status seems locked inside the History table and I can't seem to figure out how to get it into the query results?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is that "newvalue" can be different between the different rows of "quoteid",
so you need to decide which value/row to take.

show some data sample of the raw data, and the expected output.
my article explains this, actually, and I can just emphasize that you just, but first, need to clarify those "rules", and then translate them into sql as shown in the article.

you might need to join the history table 2 times...
0
 
Christopher GordonSenior Developer AnalystCommented:
I'm making an assumption on your requirements with the attached code. Even if it works, it might not be what you're looking for.   Don't worry this doesn't meet your needs it didn't  take me any time at all.

I missed a "()" in the common table expression.  You can try it again.
with Quote_Instances as
(
	select	*, ROW_NUMBER() over (Partition By QuoteId Order By DateChanged DESC) as Instance_Counter
		
	from	History
	
	where  (Class = 'ProductionStatus' OR Class = 'AccountingStatus') 
)

, Quote_Instance_Recent as
(
	 select *
	 from	Quote_Instances
	 where	Instance_Counter = 1
)


select 
	m.[Quote]
,	m.[CustomerNumber]
,	m.[Customer]
,	m.[OrderReserveNumber]
,	m.DateChanged
,	quotes_recent.NewValue

from MasterQuoteRec m 

left outer join Quote_Instance_Recent quotes_recent on
	m.Quote = quotes_recent.QuoteId

Open in new window

0
 
dparkesAuthor Commented:
Excellent solution.  Just what I was looking for.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now