Select unique max value | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select unique max value

I will try to explain my problem: In a table for companydata. Each company has several rows (lots of data for the same company). Each row has until now been unique by a mnemonicId. I now would like to add a history possibility by adding a new column =version. So each mnemonicId can be stored in several rows for each company. But I then only want to select one value for each mnemonic per company and that should be the maximum version number per each mnemonic. I want all mnemonics that is placed in a order table so if the value is not in the table where I store the values NULL is returned. So I allways want a specific number of rows dependent on the order table. But only one value from the storing table based on the version number now before changing the select statement I get this when running the SP when added more than 1 value per mnemonic per company Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Here is the SQL code witha CompanyId=294 as a test SELECT
294 AS CompanyId,
MED.Med_Id as MetadataId,
MED.MED_mnemonicas Mnemonic,
MED.MED_shortas Short,
MED.MED_formatas format,
MED.MED_typeas MetaDataType, (SELECT CDX.CDX_intValue FROM
CDX_CompanyDataXX CDX
WHERE
CDX.CDX_COM_id=294
AND
CDX.CDX_MED_id=MED.Med_Id
(select MAX(CDX_version)from CDX_CompanyDataXX
where CDX_COM_id=294)

)
AS lngValue FROM
MED_MetaData MED
JOIN
CDO_CompanyDataXXorder CDO ON MED.MED_id=CDO.CDO_MED_id WHERE
MED.Med_Type=3
or MED.MED_type=4
or MED.MED_type=2
or MED.MED_type=7 order by CDO.CDO_COD_id If I add following code (select MAX(CDX_version)from CDX_CompanyDataXX
where CDX_COM_id=294) I get an answer but then version is based on the max for all the company values but I need it to be selected unique for each mnemonic. Is that possible to code?

This is a little hard to understand coming from the outside … I now would like to add a history possibility by adding a new column =version. So each mnemonicId can be stored in several rows for each company. But I then only want to select one value for each mnemonic per company and that should be the maximum version number per each mnemonic. It looks a bit as if you’re trying to convert a Company address table into an Orders data entry table. You should not store any information related to actual orders with the address (although there is some stuff, like standard discount rates, that does refer to the orders in general but not to any single order in specific, and you should indeed store that with the address). I want all mnemonics that is placed in a order table so if the value is not in the table where I store the values NULL is returned. So I allways want a specific number of rows dependent on the order table. But only one value from the storing table based on the version number The kind of information that you seem to be searching for does not have to be stored in a separate table: it can at all times be derived from the existing information about a company’s orders, using a query. For example this gives you the details of the last order for each companies, with Nulls if there is no order for the company: SELECT C.CompanyName, FinalOrder.*
FROM Company AS C
LEFT JOIN
(SELECT O.OrderId, O.CompanyId FROM Orders O
WHERE O.OrderId = (SELECT O1.OrderId FROM Orders O1 WHERE O1.COmpanyId = O.CompanyId
AND O1.Version = (SELECT MAX(O2.Version) FROM Orders O2 WHERE O2.CompanyId = O1.CompanyId)))
AS LastOrderId
LEFT JOIN Orders AS FinalOrder ON LastOrderId.OrderId = FinalOrder.OrderId If you want to do the subquery in the WHERE clause, that is possible but that will turn the LEFT JOIN into effectively an INNER JOIN. You can code around that by adding a UNION, like so: SELECT C.CompanyName, FinalOrder.*
FROM Company AS C
INNER JOIN Orders AS FinalOrder
ON C.CompanyId = FinalOrder.CompanyId
WHERE FinalOrder.Version = (SELECT MAX(O1.Version) FROM Orders O1
WHERE O1.CompanyId = FinalOrder.CompanyId)
UNION
SELECT C.CompanyName, Orders.*
FROM Company AS C
LEFT Orders ON C.CompanyId = Orders.CompanyId
WHERE Orders.CompanyId IS NULL
I’m not really getting your answer, but i appreciate it, hence. My order table, the table that distinguise which mnemonics should be included is, the CDO_CompanyDataXXOrder. That table is affecting all companies so no CompanyId can be found there. only MED_id(s) for all companies. Then MED_metadata table has all the metadata with the name and other metadata specific data. Then the actual values is stored in the CompanyDataXX table that has reference to MED_metadata by MED_id. As I wrote I always want to get as many rows as there are in the order table (CDO_CompanyDataXXOrder) even if no values exists in the Data table (CompanyDataXX). And if there are more than ONE value for each mnemonic in the (CompanyDataXX) take the highest version for that mnemonic, So for each value for each mnemonic will depend on how many versions there are for that mnemonic in the CompanyDataXX table for a specific company. Little bit complicated I know and expecially to write down. So I need to check in CompanyDataXX for each mnemonic which version is the highest and then chose the value corresponding to that in CompanyDataXX.
An example of the result Headers: CompanyId,MetadataId,Mnemonic,Short,format,MetaData,Type,lngValue,charValue results:
294,888,XX_InvestmentCase,Investment,20,3,NULL,NULL 294,924,XX_OwnershipStructureFieldPeriod,OwnershipDate.20,3 ,NULL,NULL 294,949,XX_OwnershipFieldB7,%ofvotes 7,20,3,NULL,NULL 294,1198,XX_CreditRecommendation,CreditRecommendation,0,7,240,NULL Here we can see that only the last row has a value in CDX_CompanyDataXX table, the value 240. The others has no value and NULL is instead shown. The table CDX_CompanyDataXX has following columns CDX_COM_id CDX_MED_idC CDX_intValue CDX_decValue CDX_charValue CDX_dateValue CDX_bolValue CDX_version CDX_sOrder CDX_createdDate Depending on type of values (type in mnemonic table) either of the bold columns is used to store a value for a mnemonic. The rest of the columns is NULL for that mnemonic. See the above example where I’ve only included 2 columns. Then I added the version column that should be incremeted by one if a new value is added to the database for a company and that mnemonic. In a SP I want to only retrive one value for each mnemonic per comoepany so I want to choose the value that has the highest version number for EACH mnemonic. If only one value is stored version number =0 then if a new value is added for that company for that mnemonic the version number is adding up 1 for that row. CDO_CompanyDataXXOrder table: CDO_MED_id
A number of MetadataIds is stored here (not all). Those that should be shown in the SP I would like to fix even if no value is in the value table CDX_CompanyDataXX. MED_Metadata MED_id and several columns with definitions for all metadata

]]>