Transact SQL :: First DISTINCT Value In A Result Set
Jun 23, 2015
I created a view which gave me the TOP1000 Companies we do consulting work for.
It includes their name, rank, year, companyID. I wanted to then link this view to the address table. I was able to do this, but because one company might have multiple addresses my result set was large when I started out by just adding city to my view. How can I take my result set and just choose the first DISTINCT value in the result set? Here's an example of the result set:
Name Rank Year CompanyID City
Bill's Fish 22 2015 779 Sunrise
Bill's Fish 22 2015 779 Billings
I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat
an example for the pb 1)First i have created a dynamic cursor :
DECLARE authors_cursor CURSOR DYNAMIC FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7 OPEN authors_cursor FETCH first FROM authors_cursor
2)The result for this cursor is for expamle 'USA'.
3) If now i do an update on that location with a new value 'USA1'
update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7
4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).
If i remove DISTINCT from the cursor declaration , the process works fine .
An example for my pb 1) Created a dynamic cursor : DECLARE cursor_teste CURSOR DYNAMIC FOR Select DISTINCT name from table WHERE ID = 1 OPEN cursor_teste FETCH first FROM cursor_teste 2)The result for this cursor is for example 'teste'. 3) If now i do an update on that name with a new value 'teste1' than if i fetch the cursor , i''ll get the old value (teste) .
any idea how to make a select distinct result in a dynamic Cursor?
there is a many to many relation between the tables. Each Policy can have 1 or many Liability Constraints. Each Liability Constraint can be attached to 1 or many policies.
What I need to do is get the distinct combination of LcCode, LcDesc, LcAmt, LcFreq and LcSetId from the Liability table as well as the earliest PolInceptionDt from any of the policies associated with the Liability Constraint using only the distinct combination of LcCode, LcDesc, LcAmt, and LcFreq.
Give the data above, I would need to end up with the following result:
I tried to use the Row_Number functionand join using PolNum, but I keep getting the wrong results back.
I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.
I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04
I compiled the code in order to get the information I want from 2 tables:
Source Table SELECT COUNT(Id), LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7) FROM IncidentDimNew
[Code] ...
This is the code I wrote in order to do it
INSERT INTO IncidentDim [Id] ,[CreatedDate] SELECT [Id] ,[CreatedDate]
FROM IncidentDimNew
where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'
GO
But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.
So how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files ,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected FROM dbo.FILE_MASTER GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE HAVING COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount Id 1000 1000 Name 1000 1000 Phone 600 600 PINCode 200 1000 City 400 1000 Gender 2 1000
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:
Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue.
But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
DECLARE @c NVARCHAR(MAX) WITH c1 AS ( SELECT [abcd] AS table_name FROM [intranet].[dbo].[soccerfieldinfo] where [abcd] IS NOT NULL ), c2 AS ( SELECT Row_Number() OVER (ORDER BY table_name) AS r
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups). The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1 Union Select Distinct([favoritesport]) from table2 Union Select Distinct([favoritesport]) from table3 etc etc etc
I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.
Cid First_Name 1 Alex 2 Bob 3 Carrie
I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.
Pid Product_Name 1 Gin 2 Scotch 3 Vodka
And I have a table that holds purchase called Customer_Purchases that contain the following records:
Cid Pid 1 1 1 2 2 1 2 3 3 2
I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).
I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :
SELECT DISTINCT Pid, Cid FROM Customer_Purchases WHERE (Cid = 1) OR (Cid = 3) OR (Cid <> 2)
I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01' declare@enddate date = '2014-05-01' ; with SM as ( SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row, u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL )) ) INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id AND s.Service_Delivery_Unit_Id = 200 ) SELECT count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
I am writing a query where I am identifying different scenarios where data changes between one week and the next. I've set up my result set in the following manner:
PrimaryID Field Changed Previous Value New Value 10003 SKUName SKU12345 SKU56789 10003 LocationId Den123 NYC987 etc...
The key here being that in the initial resultset ID 10003 is represented by one row but indicates two changes, and in the final output those two changes are being represented by two distinct rows. Obviously, I will bring in the previous and new values from a source.
We have multiple employees assigned to the same assignment sometimes, and my query is skewed as that if there are 2 people assigned to the ssame assigment I can only see the amt of time worked for the 1st person. What should I do to remedy this?
I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1] From ( SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, * From[dbo].[Table1] ) A WhereID > 1 OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
select name,number,city from student where name='abcd' now name='abcd' is not there in student table so result is no record.
I need record for this query, all the columns as null or empty. how to do this one? Because am using this query in report so this query as null or empty i will replace as 0 for rows.
I need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
Periodically throughout the day a report is manually pulled from a SQL Server database. Is their a way w/o me adding a field to the database to have the result set return the "new" results? For example, lets say this is our DDL
Create Table OneTwoThree ( id int ,date11 datetime ,firefly varchar(10) )
Prior I could use this syntax, but that was only with needing to generate 1 result set.
Select id, convert(varchar(10), date11, 101) As [Date], firefly from onetwothree where CONVERT(varchar(10), date11, 101) < CONVERT(varchar(10), GetDate(), 101)
Looking at my datetime values, let's say the 1st was generated at 18:45, obviously the 1st two records in the table would be returned. And let's say a 2nd time I need to generate I want to exclude the 1st two entries as they have already been verified. How can I do such w/o adding a field to the table?
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID inner join ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product ( SalesManID int, ProductID int ) INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4) INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008 ;WITH CTE AS ( SELECT Case WHen left(MONTHDate,3)='Jan' Then '01' WHen left(MONTHDate,3)='Feb' Then '02' WHen left(MONTHDate,3)='Mar' Then '03' WHen left(MONTHDate,3)='Apr' Then '04' WHen left(MONTHDate,3)='May' Then '05'
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet] GO /****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[usp_sysconf]
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
