locked
If I have multiple records in a table with the same name, but different values, how do I pull the one out with a specific value and not show the other? RRS feed

  • Question

  • User-858993849 posted

    Lets say I have a  column in 3 different records of a table with the same value,  but a another column in the table with  different values (4,5,6) for each record.  How do I return the record for the 4 only if a record value doesn't exist of 5 or 6.

    I only want to select the record with value of 4 if there isn't one with the value of 5 or 6.

    Tuesday, November 8, 2016 1:52 PM

All replies

  • User-1716253493 posted

    Try this

    SELECT * FROM TBL WHERE ID IN (SELECT MIN(ID) FROM TBL GROUP BY COLNAME)

    Tuesday, November 8, 2016 2:57 PM
  • User-858993849 posted

    Thanks for the quick reply!  I tried it, but get this as an error.

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Tuesday, November 8, 2016 3:26 PM
  • User364663285 posted

    Thanks for the quick reply!  I tried it, but get this as an error.

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Please ensure there is ONLY one column in your sub-query.

    Thursday, November 10, 2016 7:02 AM
  • User-271186128 posted

    Hi JAYHAWKER,

    Lets say I have a  column in 3 different records of a table with the same value,  but a another column in the table with  different values (4,5,6) for each record.  How do I return the record for the 4 only if a record value doesn't exist of 5 or 6.

    I only want to select the record with value of 4 if there isn't one with the value of 5 or 6.

    Based on your description, why not use where clause and use the Name and Value field to query the data table? Code like this:

    create table Test
    (
    Name nvarchar(30),
    [Value] int
    )
    go
    
    insert into Test
    select 'AAA', 4 union
    select 'AAA', 5 union
    select 'AAA', 6 
    go
    select  * from Test where Name = 'AAA' and [Value] = 4    --output: AAA 4
    go
    --or
    select top 1 * from Test where Name = 'AAA' and [Value] = 4   --output: AAA 4
    go

    If I misunderstand your problem, please feel free to let me know.

    Best regards,
    Dillion

    Thursday, November 10, 2016 7:08 AM
  • User-858993849 posted

    I may not be understanding this correctly, but I think this will find a record with a name has a value of 4, it will still show even if it has a value of 5 or 6. I only want the record with the same name and a value of 4 if one doesn't have a value of 5 or 6.

    Like I said, I could be wrong and your code maybe does that, but I couldn't understand how. IF it does, then if you could please explain it to me I'd really appreciate.

    Thanks!

    Thursday, November 10, 2016 12:16 PM
  • User475983607 posted

    Sounds like you need an IF...

    IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    	DROP TABLE #Test
    
    create table #Test
    (
    	Name nvarchar(30),
    	[Value] int
    )
    go
    
    insert into #Test
    select 'AAA', 4 union
    select 'AAA', 5 union
    select 'AAA', 6 
    
    If EXISTS(SELECT (1) FROM #Test WHERE [Value] = 4)
    	SELECT Name, [Value] FROM #Test WHERE [Value] = 4
    ELSE
    	SELECT TOP 1 Name, [Value] FROM #Test WHERE [Value] <> 4  ORDER BY [Value]

    Thursday, November 10, 2016 12:38 PM
  • User-967720686 posted

    Hi, 

    Try the code below. 

    Declare @Tab Table (
    	Name	varchar(30),
    	[Value] int
    )
    
    insert into @Tab
    select 'AAA', 4 union
    select 'AAA', 5 union
    select 'AAA', 6 union
    Select 'BBB', 4 union
    Select 'BBB', 8 union
    Select 'BBB', 9 
    
    
    Select	A.*
    From	@Tab A 
    Where	A.Value = 4
    And		Not Exists (Select Top 1 'X' From @Tab Where A.Name = Name And Value In (5,6))			
    

    Friday, November 18, 2016 4:34 AM