January 3, 2017 at 2:17 pm
New to this board.... not sure if Access SQL is discussed here, but I need this answer to be resolved for Access SQL.
I have a field that has 5 different values, for simplicity lets call them ValueA, ValueB, etc.
So now for each ID im getting 5 records, one for each value. But all 5 values do not always exist.
What I want is to say I want ValueA, and if it doesn't exist then ValueB, and if it doesn't exist then ValueC etc, so that for each ID I only return one entry.
January 4, 2017 at 7:53 pm
brianmsbc (1/3/2017)
New to this board.... not sure if Access SQL is discussed here, but I need this answer to be resolved for Access SQL.I have a field that has 5 different values, for simplicity lets call them ValueA, ValueB, etc.
So now for each ID im getting 5 records, one for each value. But all 5 values do not always exist.
What I want is to say I want ValueA, and if it doesn't exist then ValueB, and if it doesn't exist then ValueC etc, so that for each ID I only return one entry.
It's kind of impossible to say when no one can see the table or tables. You really should post DDL and sample data for these type of questions. It's hard to understand what you mean by getting 5 records (rows) for each ID but all 5 don't exist. Then how are you getting 5 rows, one for each value? That makes no sense.
If you post the DDL and sample data, some of that can be easier to figure out. When you have ValueA, ValueB,etc that appears to be a character value column of some type. That is very different if those values are really are integers. The query would be different.
If I were to guess, you just need an aggregate function and and group by based on what you just wrote there. If IDs were integers, Value was varchar and this is SomeTable, then it would be something like:
SELECT ID, min(Value)
FROM SomeTable
GROUP BY ID
Sue
January 6, 2017 at 1:24 pm
Is this what you are looking for?
--Test table
--Test table
DECLARE @sometable TABLE (ID INT IDENTITY(1,1), Value VARCHAR(10))
INSERT INTO @sometable(Value)VALUES
(NULL),('ValueB'),('ValueC'),('ValueD'),('ValueE')
--1st value is null
SELECT * FROM @sometable
--pick the top 1 which has a value
SELECT TOP 1 ID, Value FROM @sometable WHERE Value IS NOT NULL ORDER BY ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply