Declare table, insert into table, select distinct

I have a table-valued function where there are multiple tables being declared and finally joined together to create the final table with data from each table. I had a problem with duplicates so I created a new table @ORDERHEADER so select the distinct values and then insert it into the the table and eventually the final table. When I run the select distinct by itself, it populates correctly but when I run it with the insert nothing populates.

Below is the code I am currently working with (I kept the commented out lines for you to see what I have tried — above the first “–LEFT OUTER JOIN” is my working code).

DECLARE @ORDERHEADER TABLE(     [DIV_CD] [varchar](8) NULL,     [SALES_ORD_NR] [varchar](8) NOT NULL,     [CUST_EDP_ID] [decimal](28,0) NOT NULL,     [OFFER_CD] [varchar] (8) NULL)      INSERT INTO @ORDERHEADER     SELECT DISTINCT(SV_OR_1000_ORDER_HEADER.SALES_ORD_NR),  SV_OR_1000_ORDER_HEADER.DIV_CD, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID,  SV_OR_1000_ORDER_HEADER.OFFER_CD     from SV_OR_1000_ORDER_HEADER     --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  r.SALES_ORD_NR                 --LEFT OUTER JOIN  OPENQUERY(CH1KNOWAPP,'SELECT DISTINCT SALES_ORD_NR FROM  EmailVPN..EB_RETURN_CONFIRMATION_NEW WITH(NOLOCK) WHERE  ORD_ITM_STA_LGCY_CD IN (''R'',''E'',''4'')') as RET_EMAILS on  RET_EMAILS.SALES_ORD_NR COLLATE SQL_Latin1_General_CP1_CI_AS =  SV_OR_1000_ORDER_HEADER.SALES_ORD_NR     --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  r.SALES_ORD_NR      --join SV_MACORD_RETURNS on  SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = SV_MACORD_RETURNS.SALES_ORD_NR     -- where exists (SELECT DISTINCT * from  SV_OR_1000_ORDER_HEADER)-- exists (select * from SV_OR_1000_ORDER_HEADER  join SV_MACORD_RETURNS on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  SV_MACORD_RETURNS.SALES_ORD_NR)      --where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR is not null     --where  SV_OR_1000_ORDER_HEADER.OFFER_CD not in ('36')      where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = 'W3151536'     --group by SV_MACORD_RETURNS.SALES_ORD_NR,  SV_MACORD_RETURNS.DIVISION, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID, SV_MACORD_RETURNS.OFFER_CD     --having COUNT(SV_OR_1000_ORDER_HEADER.SRC_SYS_TRANS_DT) < 2 

How do I limit query results, when distinct isn’t distinct?

I have a bill of material file that I am trying to reduce to only the unique parts, and related data for the line. The problem I’m running into is multiple instances of a part number due to variations in the formatting or language in the part name from the system/s that a third party pulls the data from. pn123 part_name pn123 Part-name pn123 Part name pn123 German name

All other fields I select are equal, how do I limit this in the where clause to just one instance of the above for all different part numbers? Is there an equivalent to MAX() in a text string?

I am working around the issue in excel, by deleting the dupes.

select distinct  adhoc.ats_esh.Customs_Entry_Num [VIN]as [Qlickview VIN] ,[Build_Date] ,[BOM_Level] ,[9802].[Supplier] ,[Part_number] ,[Part_Name] *******THIS IS THE PROBLEM FIELD******* ,[Unit_Price] ,[Usage] ,[Extended_Price]  from    adhoc.IMFM_9802_EU_AP [9802] inner join ADHOC.ATS_ESL     ON [9802].VIN = ADHOC.ATS_ESL.Part_Num         inner join adhoc.ATS_ESH     ON ADHOC.ATS_ESH.Trans_SK = ADHOC.ATS_ESL.Trans_SK        where  adhoc.ats_esh.importer ='ACME_CO' and adhoc.ATS_ESH.ENTRY_SUMMARY_DATE >= '2/01/2018' And adhoc.ATS_ESH.ENTRY_SUMMARY_DATE < '3/01/2018' AND adhoc.ats_esl.Supplier in('supplier1','supplier2','supplier3')  --and adhoc.ats_esl.Part_Num like '%ABC%'  --and [BOM_Level] = '1' --**** use MAX() 

Group with Character Degrees {1,pq,pr,qr}, where p,q and r are distinct primes

I am currently trying to bound the derived length of certain solvable groups assuming that they have only two irreducible monomial complex character degrees. Using induction, it often suffices to consider groups $ G$ satisfying that its set of irreducible character degrees, $ \textrm{cd}(G)$ , satisfies

$ \textrm{cd}(G)=\lbrace 1,pq,pr,qr\rbrace$

where $ p,q$ and $ r$ are distinct primes. I would in particular like to know whether these (solvable) groups have derived length at most 3, but I have been unable to prove this. The paper

M.L.Lewis. Determining Group Structure from Sets of Irreducible Character Degrees – Journal of Algebra (1997)

studies similar groups, where Mark Lewis is able to prove strong statements about the groups he studies. This might suggest that it is reasonable to think that one can also put similar restraints on the groups above (and in particular bound its derived length by 3).

Django retrieve rows for the distinct column values

I want to query Model rows in Django,

class Language(models.Model):     language_id = models.CharField(max_length=100, default="")     code = models.CharField(max_length=100,default="")     name = models.CharField(max_length=500, default="") 

In this table, the language_id is not unique, for example, below is the sample data

 language_id | code | name     12345       en    english    12345       te    telugu    54321       en    english    54321       te    telugu 

I want to filter the rows(all columns) which should have distinct language_ids.

What currently I am doing.

    language_list = Language.objects.all()      list = []     idlist = []     for language in language_list:         if language.language_id not in idlist:             il = language             list.append(il)             idlist.append(language.language_id) 

then list will have all the distinct rows(model objects).

Is there any better way to do this. I don’t want to rotate through all the language models.

Is this a valid proof of Hall’s Theorem on System of Distinct Representatives?

Hall’s Theorem states that for sets $ A_1,…,A_n$ if and only for all $ J\subseteq \{1,…,n\}$ we find $ |\bigcup_{j\in J}A_j|\geq|J|,$ then we can choose $ x_i\in A_i$ for all $ i$ such that $ \{x_1,…,x_n\}$ is a system of distinct representatives for $ A_1,…,A_n.$ To me it seems easiest to some inductive argument (or a contradiction that is similar to induction), however, in my graph theory class we’re working with Flow Algorithm’s, so the goal is to solve it using the Max-Flow-Min-Cut Theorem. I’ve seen one proof using that theorem, but I think I’ve came up with another, which to me seems more natural. I was wondering if my proof is correct. Here’s the proof.

$ \textbf{Proof:}$

Throughout $ x$ shall refer to an element of some $ A_i.$ We set up a capicated network as follows $ $ \overrightarrow{E}=\{(s,A_i):\forall A_i\}\cup\{(A_i,x):\forall A_i\text{ and }x\in A_i\}\cup\{(x,t):\forall x\in \cup_{i=1}^n A_i\}$ $ with capacities for $ (s,A_i)$ and $ (x,t)$ all $ 1$ and all other capacities infinite. This is ‘depicted’ in the following link, where any unwritten capacity is infinite.

View post on imgur.com

Now we suppose that we have a minimal network cut $ U.$ So the sum of capacities of edges leaving $ U$ is minimal. If $ A_i\in U,$ and $ x\not\in U$ for sum $ x\in A_i,$ then $ (A_i,x)$ is an edge leaving $ U$ with infinite capacity, and $ \{s\}$ has smaller capacity than $ U,$ impossible. So $ x\in U$ for all $ x\in A_i$ . We compute $ $ \text{capacity}(U)=\sum_{A_i\not\in U}\text{capacity}(s,A_i)+\sum_{x\in U}\text{capacity}(x,t)=|\{A_i\}\backslash U|+|\{x:x\in U\}|$ $ $ $ \text{capacity}(U)\geq|\{A_i\}\backslash U|+|\{x\in A_i: A_i\in U\}|\geq|\{A_i\}\backslash U|+|\{A_i\in U\}|=n.$ $ Thus since $ \text{capacity}\{s\}=n$ we find that $ \{s\}$ is a min cut, and we can find a system of distinct representatives. $ \blacksquare$

Can closure and complement generate 14 distinct operations on a topological space if no subset generates more than 6 distinct sets?

$ \newcommand{\XT}{(X,\mathcal{T})}$ From Definition 1.2 in Gardner and Jackson (GJ): The $ K$ ‑number $ K(\XT)$ of a topological space $ \XT$ is the cardinality of the Kuratowski monoid of operators on $ \XT$ generated by closure $ b$ and complement $ a$ under composition. For any $ A\subset X,$ the $ k$ ‑number $ k(A)$ of $ A$ is the cardinality of the family of subsets generated by $ A$ under $ \{a,b\}.$ The $ k$ ‑number of the space is $ $ k((X,\mathcal{T}))=\max\{k(A):A\subset X\}.$ $

Question. What values does the ordered pair $ (K(\XT),k(\XT))$ take?

Partial Answer. Lemma 2.8 in GJ states that $ k(\XT)=4$ iff $ \XT$ is a non-discrete door space or $ \mathcal{T}\setminus\varnothing$ is a filter in $ 2^X.$ Each implies $ K(\XT)<14,$ hence $ (14,4)$ is impossible (GJ p. 25).

The case $ (14,6)$ only gets mentioned once in GJ, on p. 28: $ $ \unicode{x201C}\text{We do not know of … any Kuratowski space with }k\text{-number }6.\unicode{x201D}$ $ (A Kuratowski space is one that satisfies $ K(\XT)=14.)$

It is well known that $ k(A)$ is always even (GJ p. 15). It follows from the definitions and the identity $ bababab=bab$ that $ $ 2\leq k(\XT)\leq K(\XT)\leq14.$ $

By Theorem 2.1 in GJ, the only possible values of $ K(\XT)$ are 14, 10, 8, 6, 2. Clearly, $ k(\XT)=2$ iff $ \XT$ is discrete. Thus, besides possibly $ (14,6),$ the pair can only be:

$ (2,2),$
$ (6,4),$ $ (6,6),$
$ (8,4),$ $ (8,6),$ $ (8,8),$
$ (10,4),$ $ (10,6),$ $ (10,8),$ $ (10,10),$
$ (14,8),$ $ (14,10),$ $ (14,12),$ $ (14,14).$

Each occurs in some space $ \XT$ with $ |X|\leq7$ (this holds for both Kuratowski monoids that satisfy $ K(\XT)=10).$ Examples are listed below.

Theorem 2.10 in GJ states that for any $ A\subset X,$ the family of subsets generated by $ A$ under $ \{b,i\}$ where $ i$ denotes interior satisfies exactly one of 30 possible collapses of the Hasse diagram:

$ \hspace{268px}$

The table below lists these collapses in the same order as they appear in Table 2.1 in GJ. Each entry is labeled by what I am calling the $ h$ ‑number of $ A$ , or $ h(A).$ The identity operator is denoted by $ \textsf{id}.$

\begin{array}{|c|c|c|c|} \hline h(A) & h(aA) & \text{collapse} & k(A) \ \hline 1 & 1 & \varnothing & 14 \ \hline 2 & 3 & bi=ibi & 12 \ \hline 3 & 2 & ib=bib & 12 \ \hline 4 & 5 & bib=b & 12 \ \hline 5 & 4 & ibi=i & 12 \ \hline 6 & 6 & ib=ibi,\ bi=bib & 10 \ \hline 7 & 7 & ib=bib,\ bi=ibi & 10 \ \hline 8 & 9 & ib=bib,\ ibi=i & 10 \ \hline 9 & 8 & bi=ibi,\ bib=b & 10 \ \hline 10 & 11 & bi=ibi=i & 10 \ \hline 11 & 10 & ib=bib=b & 10 \ \hline 12 & 12 & bib=b,\ ibi=i & 10 \ \hline 13 & 13 & ibi=bi=ib=bib & 8 \ \hline 14 & 16 & ib=ibi=i,\ bi=bib & 8 \ \hline 15 & 17 & ib=bib,\ bi=ibi=i & 8 \ \hline 16 & 14 & ib=ibi,\ bi=bib=b & 8 \ \hline 17 & 15 & ib=bib=b,\ bi=ibi & 8 \ \hline 18 & 19 & bi=ibi=i,\ bib=b & 8 \ \hline 19 & 18 & ib=bib=b,\ ibi=i & 8 \ \hline 20 & 21 & ibi=bi=ib=bib=i & 6 \ \hline 21 & 20 & ibi=bi=ib=bib=b & 6\ \hline 22 & 22 & ib=ibi=i,\ bi=bib=b & 6 \ \hline 23 & 24 & \textsf{id}=b,\ ib=ibi=i,\ bi=bib & 6 \ \hline 24 & 23 & \textsf{id}=i,\ bi=bib=b,\ ib=ibi & 6 \ \hline 25 & 25 & ib=bib=b,\ bi=ibi=i & 4,6 \ \hline 26 & 27 & \textsf{id}=bi=bib=b,\ ib=ibi=i & 4 \ \hline 27 & 26 & \textsf{id}=ib=ibi=i,\ bi=bib=b & 4 \ \hline 28 & 29 & \textsf{id}=b,\ ibi=bi=ib=bib=i & 4 \ \hline 29 & 28 & \textsf{id}=i,\ ibi=bi=ib=bib=b & 4 \ \hline 30 & 30 & ibi=bi=ib=bib=b=i=\textsf{id} & 4 \ \hline \end{array}

When $ h(A)=25,$ if $ A$ is dense with empty interior (e.g., $ \mathbb{Q}$ in $ \mathbb{R}$ under the usual topology) then $ k(A)=4,$ otherwise $ k(A)=6.$

Conjecture. Let $ A$ and $ B$ be subsets of a topological space. If $ $ \tag1h(A)\in\{22,23,24,26,27\}\text{ and }h(B)=25,$ $ then $ $ \tag2\min\{h(A\cup B),h(A\cap B),h(A\cup aB),h(A\cap aB)\}<20.$ $

If true, the conjecture implies $ (14,6)$ is impossible. For, suppose $ \XT$ is a Kuratowski space with $ k$ ‑number 6. Since $ K(\XT)=14,$ there exist subsets $ A$ and $ B$ of $ X$ such that $ biA\neq ibiA$ and $ ibB\neq ibiB.$ Since $ k(\XT)=6,$ it follows from the table that $ (1)$ holds. The conjecture then contradicts $ k(\XT)=6.$

Computer experiments have verified the conjecture for all 2450 inequivalent $ \text{non-}T_0$ spaces such that $ 1\leq|X|\leq7$ (these were recently posted here) and roughly 40,000 others such that $ 8\leq|X|\leq16.$ We ignore $ T_0$ spaces because finite ones satisfy $ K(\XT)\leq10$ by Theorem 3 in Herda and Metzler. We also ignore sets $ A$ such that $ h(A)\in\{24,27\}$ because our C program scours entire power sets and De Morgan’s laws imply that $ A$ provides a counterexample iff $ aA$ does.

Our list of 136 known (at the time of writing) $ 4$ ‑tuples $ $ (h(A\cup B),h(A\cap B),h(A\cup aB),h(A\cap aB))$ $ satisfying $ h(A)\in\{22,23,26\}$ and $ h(B)=25$ can be found here.

A few weeks ago I posted a question here based on the case $ (26,25,30,12),$ where $ h(A)=26.$ Currently no answer has appeared.

Here is the list promised above. It is surely complete, but we lack proof. The cardinality of each space is minimal. It is assumed that $ X=\{1,\ldots,n\}$ when $ |X|=n.$

\begin{array}{|c|c|c|c|} \hline \text{pair} & |X| & \text{base for }\mathcal{T} & h\text{-numbers that occur} \ \hline (2,2) & 1 & \{\{1\}\} & 30 \ \hline (6,4) & 2 & \{\{1,2\}\} & 25,30 \ \hline (6,6) & 3 & \{\{1\},\{2,3\}\} & 25,30 \ \hline (8,4) & 2 & \{\{1\},\{1,2\}\} & 28\text{-}30 \ \hline (8,6) & 3 & \{\{1\},\{1,2\},\{1,2,3\}\} & 20,21,28\text{-}30 \ \hline (8,8) & 4 & \{\{1\},\{2\},\{1,3\},\{2,4\}\} & 13,28\text{-}30 \ \hline (10,4) & 3 & \{\{1\},\{2\},\{1,2,3\}\} & 26\text{-}30 \ \hline (10,6) & 4 & \{\{1\},\{2\},\{1,2,3\},\{1,2,4\}\} & 22,26\text{-}30 \ \hline (10,8) & 4 & \{\{1\},\{2\},\{1,3\},\{1,2,4\}\} & 14,16,23,24,26\text{-}30 \ \hline (10,10) & 5 & \{\{1\},\{2\},\{1,3\},\{2,4\},\{1,2,5\}\} & 6,14,16,23,24,26\text{-}30 \ \hline (14,8) & 4 & \{\{1\},\{2,3\},\{1,2,3,4\}\} & 18,19,26\text{-}30 \ \hline (14,10) & 5 & \{\{1\},\{2,3\},\{1,4\},\{1,2,3,5\}\} & 10,11,14,16,18,19,23,24,26\text{-}30 \ \hline (14,12) & 6 & \{\{1\},\{2\},\{3,4\},\{1,5\},\{1,2,6\}\} & 4,5,12,14\text{-}17,23\text{-}30 \ \hline (14,14) & 7 & \{\{1\},\{2\},\{3,4\},\{1,5\},\{2,6\},\{1,2,7\}\} & 1,4,5,6,12,14\text{-}17,23\text{-}30 \ \hline \end{array}

Trouble with Select Distinct and Union

I am trying to sum age range counts from two tables. I was able to get the totals from both tables to show into one view, but can not figure out how to get evac center duplicates out and have all totals on one row for each evac center.

When I add a Select Distinct to each table I get errors.

SELECT dbo.tblGenPopRegistration_DEV.EvacCenter AS [Evac Center], SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age < 6 THEN 1 ELSE 0 END) AS [5 and Under], SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age BETWEEN 6 AND 17 THEN 1 ELSE 0 END) AS [6 to 17], SUM(CASE WHEN dbo.tblGenPopRegistration_DEV.Age > 17 THEN 1 ELSE 0 END) AS [18 and Over] FROM dbo.tblGenPopRegistration_DEV WHERE (dbo.tblGenPopRegistration_DEV.CheckedIn = 1) Group by dbo.tblGenPopRegistration_DEV.EvacCenter UNION ALL SELECT dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter AS [Evac Center], SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age < 6 THEN 1 ELSE 0 END) AS [5 and Under], SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age BETWEEN 6 AND 17 THEN 1 ELSE 0 END) AS [6 to 17], SUM(CASE WHEN dbo.tblGenPopAdditionalRegistrations_DEV.Age > 17 THEN 1 ELSE 0 END) AS [18 and Over] FROM dbo.tblGenPopAdditionalRegistrations_DEV Group by dbo.tblGenPopAdditionalRegistrations_DEV.EvacCenter