return only specified row from multiple rows

3073 views mysql
0

Question's title is a bit off,

For stater GROUP BY is not what I am looking for.

Table

+-----+---------+-------+
| ID  |  Name   |  Year |
+-----+---------+-------+
| 1   |  Foo    |  2018 |
| 1   |  Foo    |  null |
| 1   |  Foo    |  2017 |
| 2   |  Book   |  null |
| 3   |  Keys   |  null |
| 4   |  John   |  2017 |
+-----+---------+-------+

SQL

SELECT * FROM table
WHERE ID IN (1,2,3) AND (Year = 'null' OR Year >= 2018) ;

Returns

+-----+---------+-------+
| ID  |  Name   |  Year |
+-----+---------+-------+
| 1   |  Foo    |  2018 |
| 1   |  Foo    |  null |
| 2   |  Book   |  null |
| 3   |  Keys   |  null |
+-----+---------+-------+

I want it to return:

+-----+---------+-------+
| ID  |  Name   |  Year |
+-----+---------+-------+
| 1   |  Foo    |  2018 |
| 2   |  Book   |  null |
| 3   |  Keys   |  null |
+-----+---------+-------+

So basically the idea is: if a row only contains null then return that row, but if a row contains 2018 as well as another row containing null (or just 2018) then return the 2018.

I dont want to use GROUP BY ID

answered question

It should be year is null

this table doesnt exist in my database, the syntex is fine, this was just an example i wrote

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...

The syntax isn't fine unless you legit have year with string values of null. But I agree that's beside the point.

@JNevill I understnd and agree, but my database doesnt even have those coloumns neither does have 'null' i just dont want to add the actual values and column from my database for security reason

3 Answers

4

You can just aggregate here using Max(). This doesn't work if you have more than one non-null year (unless you truly do want the max of the two records).

SELECT id, name, max(year) FROM table
WHERE ID IN (1,2,3) AND (Year IS NULL OR Year >= 2018) 
GROUP BY id, name;

I changed it to IS NULL since it seems VERY VERY unlikely that you are storing strings in a column named year and one of those string values is null

posted this
4

Gives you rows where the year is 2018, or null if there is no entry for 2018:

SELECT * 
FROM table t1
WHERE ID IN (1,2,3) 
AND (Year >= 2018
    OR (Year is null 
        and not exists (select 1 from table t2 where t2.ID in (1,2,3) and t2.Year >= 2018 and t1.id = t2.id));

posted this
13

A simple NOT EXISTS should do it:

DECLARE @t TABLE (ID int, Name varchar(100), Year varchar(100));

INSERT INTO @t VALUES
(1, 'Foo', '2018'),
(1, 'Foo', NULL),
(1, 'Foo', '2017'),
(2, 'Book', NULL),
(3, 'Keys', NULL),
(4, 'John', '2017');

SELECT *
FROM @t AS t
WHERE ID IN (1,2,3)
AND Year >= 2018
OR NOT EXISTS (
    SELECT 1
    FROM @t AS x
    WHERE ID = t.ID
    AND Year IS NOT NULL
)

posted this

Have an answer?

JD

Please login first before posting an answer.