SQLTeam.com | Weblogs | Forums

Distinct values from SQL


I get the following result with the SQL below,

Is there a way to get only one value . . the table stores the two values for the same 'acetaminophen tab' with its two synonyms as I used only one entry which is TYLENOL tab



cv3catalogitemname CIT

INNER JOIN CV3OrderCatalogMasterItem OCMI
INNER JOIN cv3order O
on CIT.OrderMasterItemGUID = O.OrderCatalogMasterItemGUID

where CIT.IsForOrder=1 and CIT.IsPrimary=0

ORDER BY O.CreatedWhen desc


So, which value for Description do you want to see, and why?


Thank you for your reply,
Here is another result, I submitted the request as tylenol tab, but the result came as below, -The result gives 2 rows as both tylenol and atasol is attached to the generic name acetaminophen tab, so its pulls up both values in the result. I cannot use distinct on one column as it does not show me the results - Here I need both tylenol tab rows , and not ATASOL tab


Is it enough to only select an exact match on "tylenol"? or is your selection criteria something more ethereal than that? e.g. "The first one, alphabetically, whatever value that is", or "The one with the lowest ID number" or "The one that has the most recent ChangedOn date"


No, this is just one example,

the structure of the table is like this,

I need to get the rows only when the user submits the order thru the synonym, which is stored in another table. As you can see the primary is acetaminophen, but users can order with the other 2 names.


That makes sense. So you just need to JOIN to the Synonym table and select only the rows where [IsPrimary] = 1


yes, I did, but the issue is when I submit the order for just TYLENOL, it picks up ATASOL also and shows that I have submitted 2 orders at the same time,

This is what I have the results in the first post.


cv3catalogitemname is your Synonym table?

You have


in your query in your first post, shouldn't that be " = 1 " ?


Agghhh ... ignore that.

You want to select the order using the Synonym but you want to display the Primary?

If so you need to join the synonym table [cv3catalogitemname] again (with a different alias), in order to get the name from the IsPrimary=1 record


Is there any way to compare 'name' and summary line after (Ordered as: . . . . .) name ?


Yes, it can be done (provided that the data is 100% consistent - both NOW and IN THE FUTURE ...)

But it would be much, much, better if the value was in its own column, not "contained within" another column. Performance will be dreadful, and the query will be "fragile" if anything should cause that column's presentation to change.


The value in the name column and after 'Ordered as' is consistent, that is the only way I can think of because of the database design


Personally I would double check that:

SELECT	[Count] = COUNT(*)
	, [Match] = SUM(CASE WHEN SummaryLine LIKE '(Ordered as: _%) _%' 
		THEN 1 
		ELSE 0 
FROM	MyTable

This should extract the relevant bit:

	CHARINDEX(')', SummaryLine)
	, LEFT(SummaryLine, CHARINDEX(')', SummaryLine)-1)
	, STUFF(LEFT(SummaryLine, CHARINDEX(')', SummaryLine)-1), 1, 13, '') + '<'
	, SummaryLine
FROM	MyTable


I am getting an error on the second query
Invalid length parameter passed to the LEFT or SUBSTRING function.

Thanks Kristen, the two values to be compared are from two different tables,

I am getting the required results with the following (except I am comparing only 13 characters after '(Ordered as:' - Posted the results too . .

-- O.CreatedWhen,

from CV3Order o
inner join cv3ordertask ot on o.guid = ot.OrderGUID
inner join cv3catalogitemNAME CIT on CIT.ORDERMASTERITEMGUID = OT.OrdercatalogmasteritemGUID
and cit.name like substring(o.SummaryLine, 14,13)
AND cit.IsPrimary=0 and IsForOrder=1
order by o.CreatedWhen desc


Were the COUNT and the MATCH in the first query the same? If not then it doesn't look like your data is consistent.


No, they are not
Count Match
127837 126


just wondering, is there any way to compare the text in synonym against the text coming after '(Ordered as: XXXXX) ?

I mean the text in synonym column against XXXXX


What do some of the "non matching" rows look like:

SELECT	TOP 100 SummaryLine
FROM	MyTable
WHERE	SummaryLine NOT LIKE '(Ordered as: _%) _%' 

In effect that is what my earlier code can do, but you have only 126 rows that MATCH that pattern, and thus 127,711 rows that don't match!

I specifically looked for a space and at least one additional character after the ")", so it might be that you have data that just ends at the ")" [EDIT: or something following with NO space]. This will check that:

SELECT	[Count] = COUNT(*)
	, [Match1] = SUM(CASE WHEN SummaryLine LIKE '(Ordered as: _%) _%' 
		THEN 1 
		ELSE 0 
	, [Match2] = SUM(CASE WHEN SummaryLine LIKE '(Ordered as: _%)%' 
		THEN 1 
		ELSE 0 
FROM	MyTable

[Match2] doesn't care whether there is anything after the ")", or not.