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

using MAX to eliminate second empty (almost) record.

I have a question. I am running a query in which I join two tables based on a relationship defined on a third table.

I am getting two rows when I add the Item column. I have used the MAX function around the Item Number column and that eliminates the second row which is empty on all relevant columns with the exemption of Item Number, but I am not sure if using this function is recommended.

I am including my query, and the results I get when I run it specifying some where and and conditions.

SELECT
	gle.[Posting Date] AS PostingDate
,	gle.[G_L Account No_] AS GL_AccountNumber
,	ve.[Item No_] AS ItemNumber
,	gle.[Document No_] AS DocumentNumber
,	SUM(gle.Amount) GL_Balance
,	SUM(ve.[Item Ledger Entry Quantity]) AS Quantity
,	SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost
,	SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost
FROM 
	NAV.[dbo].[G_L - Item Ledger Relation] rel FULL OUTER JOIN 
	NAV.dbo.[G_L Entry] gle ON 
	rel.[G_L Entry No_] = gle.[Entry No_] FULL OUTER JOIN
	NAV.dbo.[Value Entry] ve ON 
	rel.[Value Entry No_] = ve.[Entry No_] 
GROUP BY 
	gle.[Posting Date]
,	gle.[G_L Account No_]
,	ve.[Item No_] 	
,	gle.[Document No_]

Open in new window


SELECT * from GL_InventoryValuation] WHERE GL_AccountNumber = '13110' AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23' AND DocumentNumber = 'SPI03164'

I get:


2010-05-28 00:00:00.000	13110	NULL	SPI03164	-24995.95000000000000000000	NULL	NULL	NULL
2010-05-28 00:00:00.000	13110	10828	SPI03164	0.00000000000000000000	0.00000000000000000000	0.00000000000000000000	NULL

Open in new window


Really the second row only shows the Item Number, Is there an efficient method to merge the results?
0
metropia
Asked:
metropia
  • 3
  • 2
2 Solutions
 
David L. HansenProgrammer AnalystCommented:
So you think you are getting the results you want but you think there may be a more efficient way of going about it?  Or, are you worried that your approach is giving you bad results?
0
 
metropiaAuthor Commented:
hi sl8rz:

to answer, I think I am getting the results you want but you think there may be a more efficient way of going about it.

Thank you.
0
 
David L. HansenProgrammer AnalystCommented:
I will often force the one row that I'm after to the top (by using an "ORDER BY" clause, sometimes using DESC for descending order) then I can grab it by using "TOP 1" like this:

SELECT TOP 1 * FROM myTable ORDER BY dollarAmount DESC

Open in new window

This code would return only the row with the maximun dollarAmount (MAX could be used but there are times when this is better - ie. maybe you want the top 10).  Remember that ORDER BY works on text too.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
metropiaAuthor Commented:
can you put an ORDER BY within a view?
0
 
David L. HansenProgrammer AnalystCommented:
No, a View is treated like a table when querying occurs against it (even though it doesn't actually exist as a table).  So you can either handle the ORDER BY in code (C#, VB, PHP, whatever) or you can make a simple stored procedure in the database that performs the query using the ORDER BY clause.  Then you just execute that stored proc.
0
 
PortletPaulfreelancerCommented:
PostingDate        GL_AccountNumber col3    DocumentNumber
2010-05-28 00:00:00.000    13110    NULL    SPI03164    -24995.95    NULL    NULL    NULL
2010-05-28 00:00:00.000    13110    10828   SPI03164    0.00         0.00    0.00    NULL
                                    ^^^^

Open in new window

what is "col3"?
If you were to run that query for all account numbers would you always get 2 records per account?
is "col3" always NULL for one of the records?
SELECT
      *
FROM GL_InventoryValuation
WHERE GL_AccountNumber = '13110'
      AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23'
      AND DocumentNumber = 'SPI03164'

-- try these (one at a time)
-- and "col3" is null
-- and "col3" is NOT null

Open in new window

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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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