How to get unique value from multiple record in MSSQL

Question

0.00/5 (No votes)

See more:

I have below [Location] table in MSSQL.

Location	Value
loc1      	value1    
loc1      	value2    
loc2      	value1    
loc2      	value2    
loc3      	value2    
loc3      	value3    
loc4      	value3    
loc4      	value4

I want distinct value of Value like below o/p:

Location	Value
loc1      	value1    
loc1      	value2    
loc3      	value3    
loc4      	value4

What I have tried:

SQL

select Location,value from Location
group by Location,value
order by location

Posted 24-Sep-18 2:16am

jaideepsinh

Updated 24-Sep-18 2:38am

v4

Add a Solution

Comments

ZurdoDev 24-Sep-18 8:51am

What you put in the "What I have tried" section will give you the requested output, so what are you asking?

jaideepsinh 24-Sep-18 9:07am

It gives me all records i want only distinct values for location.

ZurdoDev 24-Sep-18 9:11am

But what does that mean? Distinct locations? Distinct values per location?

Because the sql you have will give you each value for each location. The sql you have will give you the output you showed you want.

[no name] 24-Sep-18 14:32pm

What I read in all the comments you like to have distinct value only for Location. So far so good, now explain he logic what you like to see for Value. Is it maybe min(Value) ... or what else?

3 solutions

Solution 1

SQL SELECT DISTINCT Statement[^]

Posted 24-Sep-18 2:25am

CPallini

Comments

jaideepsinh 24-Sep-18 9:07am

I want only distinct values for location. so only distinct not works.

Solution 2

The problem is twofold, and the first one is trivial:

Location	Value
loc1      	value1    
loc1      	value2    
loc3      	value3    
loc4      	value4

presumably should be

Location	Value
loc1      	value1    
loc2      	value2    
loc3      	value3    
loc4      	value4

If not, then it makes no sense at all.
Then you have the second, rather bigger problem.
When you use GROUP BY Location, ValueYou are not telling SQL to group by anything useful - you are saying that the values both columns must be the same to be a single group: so "loc1, value1" and "loc1, value2" are in separate groups.

What you have to do to group them, is to specify one column "Location" as the GROUP BY, and then use an aggregate function to select the remaining data (MIN, MAX, SUM, AVERAGE ...) This may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

What you want to do is a little complicated, because it's not at all obvious why you get the results you want: given that loc1 and loc2 both have the same Values, but select a different Value in the output makes it extremely difficult to work out exactly what you are trying to do ...

Posted 24-Sep-18 2:38am

OriginalGriff

Comments

jaideepsinh 25-Sep-18 2:50am

No, location should not be in order it should be like below if loc1 and loc2 record not exists:
Location Value
loc3 value1
loc3 value2
loc4 value3
loc4 value4

OriginalGriff 25-Sep-18 3:02am

Just randomly posting tiny fragments of data without any explanation of what you expect to get or why doesn't help anyone.

jaideepsinh 25-Sep-18 3:30am

That is not random post. As i explain i want first Location wise Distinct sequence value. And that is sql table so you might know data can not inserted manually and it can be anything. I just post sample data for people who are trying to help me.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

littleGreenDude · Accepted Answer · 2018-09-24T02:39:00

create table #Location (
	Location varchar(10),
	Value	varchar(20)
	)


insert into #Location select 'loc1','value1'    
insert into #Location select 'loc1','value2'    
insert into #Location select 'loc2','value1'    
insert into #Location select 'loc2','value2'    
insert into #Location select 'loc3','value2'    
insert into #Location select 'loc3','value3'    
insert into #Location select 'loc4','value3'    
insert into #Location select 'loc4','value4'


select Min(Location) as Location, Value from #Location group by Value

Results:

Location	Value
loc1	value1
loc1	value2
loc3	value3
loc4	value4

Based on comments below of additional fields including an ID field, here is an expanded example/solution:

create table #Location (
	ID int IDENTITY(1,1),
	Location varchar(10),
	Value	varchar(20),
	FieldA datetime 
	)


insert into #Location(Location, Value, FieldA) select 'loc1','value1',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc1','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc2','value1',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc2','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc3','value2',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc3','value3',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc4','value3',GETDATE()    
insert into #Location(Location, Value, FieldA) select 'loc4','value4',GETDATE()


Select ID, LocSumm.Location, LocSumm.Value, FieldA from #Location L
	inner join (
select Min(Location) as Location, Value from #Location group by Value) as LocSumm
on L.Location = LocSumm.Location and
	L.Value = LocSumm.Value
order by LocSumm.Location, LocSumm.Value

And the new result set based on this query:

ID	Location	Value	FieldA
1	loc1	value1	2018-09-24 09:48:45.700
2	loc1	value2	2018-09-24 09:48:45.700
6	loc3	value3	2018-09-24 09:48:45.700
8	loc4	value4	2018-09-24 09:48:45.703

How to get unique value from multiple record in MSSQL

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

How to get unique value from multiple record in MSSQL

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us