Tag: distinct

What is the strange behavior with SELECT COUNT (distinct CLIEND_ID) From clients_data Table?

Could you please Explain this strange behavior of count (distinct client_id) statement. for : SELECT count(distinct client_id) from clients_data where bank_name =’SABB’ and tstatus = 0 I have got 6000 rows and for SELECT count(distinct client_id) from clients_data where bank_name =’SABB’ and tstatus = 0 and cif_type = ‘CARD’ I have got 5964 and for […]

order union but keep distinct

how to query a table Store(ID,Title,Company) to get all the records where title contains “term” showing fully matched records first then partial matches. in other words if im looking for “foo” i wand records displayed as Foo and sons good foo foo bar football Herfooles Heres what i have Create PROCEDURE [dbo].[SearchUserInfo] @query nvarchar(500), @term […]

Duplicate rows, different PK

I have this query : SELECT pih.PID, piv.TSV, piv.Variable_ID, piv.Value_ID, piv.Manual_Value FROM Process_Instance_Value AS piv INNER JOIN Process_Instance_History AS pih ON piv.PID = pih.PID WHERE piv.Variable_ID IN ( SELECT Variable_ID FROM someTable WHERE Transition_ID = @someValue) AND piv.PID = @someValue I get this : My problem is that those rows are duplicate for the use […]

SQL Unique Records with multiple joins

I have three tables I need to join. The first two are easy and based on the same key. A simple outer join will work. The third is tricky. It has a similar key but must be matched on the left 14 of one key. This produces duplicates which I do not want. Essentially, I […]

Distinct and Group By – query performance

Query A SELECT Distinct ord_no FROM Orders ORDER BY ord_no Query B SELECT ord_no FROM Orders GROUP BY ord_no ORDER BY ord_no In Orders tabel, ord_no is varchar type and has duplicate. Here it’s a composite key with a identity column. May I know which query is better? How could we check the query performance […]

How to Distinct query result over 2 of 3 columns

I have a query like this: WITH CTE_KELOMPOKINFORMASI (KelompokInformasi, XBRLItem_ItemId) AS ( SELECT a.Id AS KelompokInformasi, c.XBRLItem_ItemId FROM XBRLNamespaces a INNER JOIN XBRLHypercubes b ON a.XBRLView_ViewId = b.XBRLView_ViewId INNER JOIN XBRLHypercubeDimensionItems c ON b.XBRLHypercubeId = c.XBRLHypercube_XBRLHypercubeId WHERE a.Id like ‘%KBIK_AAKL%’) SELECT f.KelompokInformasi, e.Name AS DimensionName, ROW_NUMBER() OVER(PARTITION BY e.Name ORDER BY e.Name DESC) AS ‘Number’ […]

COUNT() function and SELECT with DISTINCT on multiple columns

Hoping someone can help me figure why I’m getting a syntax error here. I’m counting all rows that have two distinct column values. select count (*) as ‘Distinct’ from ( select distinct p.IDD, p.num from PERF1 p inner join MAST1 m on (m.id_table = p.id_table and m.SOURCE_TABLE = p.SOURCE_TABLE) where m.DATE > ‘2012-12-31′ ) I’m […]

Selecting distinct values from table using two columns

I have following data in the table. Id Name 1 Abc 2 Abc 3 Xyz 4 Xyz 5 def 6 def I want following results from the query Id Name 1 Abc 2 Xyz 3 def I want to avoid duplicates in the name column. Any help is greatly appreciated. Select distinct id, name from […]

Count query help needed with unique records

I have this following data-structure and I am trying to find unique ID where value Alphabets value Equals to A and B (i.e. select ID = 1, but not ID = 3 or 5). and also find unique ID where value Alphabets values contains Both A and B (i.e. select ID = 1, 3 and […]

SQL grouping by one column

I’m trying to combine columns from two tables, and have it only display only unique name (Full Name) on each row while removing duplicate names. I have used SELECT DISTINCT which works to an extent, but it shows unique names for each (File ID). This still leaves me with duplicates as a whole. I originally […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.