CODEWITHSUNDEEP
sqlsql-server-2008
neuquen
neuquen

Reputation: 4169

SQL compare min value in one column with max date in different column

I would like to find all records in Column A where, after sorting Column C in ascending order, Column D begins with a value other than the earliest date.

From the below example, I would want it to return records for Ex2 and Ex3 and not return records for Ex1.

REVISED UPDATES/REQUIREMENTS:

1) I would like it grouped by Column A and ordered by Column C.
2) I would like to find all records in Column A where the first value in Column D is not the lowest date.

Column A  Column B  Column C  Column D
--------  --------  --------  --------
  Ex1     Title A      1      2003/1/1
  Ex1     Title B      2      2003/2/2

  Ex2     Title C      3      2004/4/4
  Ex2     Title D      4      2004/3/3

  Ex3     Title E      5      2005/6/6
  Ex3     Title F      6      2005/5/5

Any Ideas?

Upvotes: 1

Views: 4743

Answers (5)

criticalfix
criticalfix

Reputation: 2870

You appear to assume an ordering by [Column B], which you can make explicit in a self join:

SELECT t1.[Column A], t1.[Column B], t1.[Column C], t1.[Column D]
FROM YourTableName t1
JOIN YourTableName t2
ON t2.[Column A] = t1.[Column A]
AND t2.[Column B] > t1.[Column B]
WHERE t2.[Column C] > t1.[Column C]
AND t2.[Column D] < t1.[Column D]

This assumes there are exactly two rows where [Column A] = 'Ex1', etc. If you have more than two rows with the same value in [Column A] you will probably find the results to be unexpected.

Note that the first two comparisons are part of the join condition, and the third and fourth comparisons are part of the WHERE clause.

UPDATE:

Working to changed requirements: There may be twenty rows with [Column A] = 'Ex1'. Return distinct values of [Column A] for which the lowest value of [Column C] is not in the same row as the lowest value of [Column D]. [Column B] is not relevant.

SELECT DISTINCT t1.[Column A]
FROM YourTableName t1
JOIN 
(SELECT [Column A], MIN([Column C]) AS MinC, MIN([Column D]) AS MinD
 FROM YourTableName
 GROUP BY [Column A]) t2
ON t2.[Column A] = t1.[Column A]
WHERE t1.[Column C] = t2.MinC
AND t1.[Column D] <> t2.MinD

This returns:

Column A
Ex1

For the following test table:

CREATE TABLE YourTableName
([Column A] VARCHAR(50),
[Column B] VARCHAR(50),
[Column C] INT,
[Column D] INT)

And test data:

INSERT INTO YourTableName
([Column A], [Column B], [Column C], [Column D])
VALUES ('Ex1', 'Title A', 1, 2)

INSERT INTO YourTableName
([Column A], [Column B], [Column C], [Column D])
VALUES ('Ex1', 'Title B', 2, 1)

INSERT INTO YourTableName
([Column A], [Column B], [Column C], [Column D])
VALUES ('Ex1', 'Title C', 3, 1)

Upvotes: 2

David Marx
David Marx

Reputation: 8558

New answer specifically addressing the following specifications (which seem to comprise the problem now):

UPDATE: I would like it grouped by Column A and ordered by Column C.

UPDATE #2: I want to return all examples from Column A where the "first" value in Column D is not the lowest value.

SELECT a.*
FROM tableName a
WHERE a.columnD <> (
    SELECT min(columnD)
    FROM tableName b
    WHERE a.columnA=b.columnA
    )
ORDER BY a.columnC

Upvotes: 1

ktaria
ktaria

Reputation: 461

select * from table where C = 
(select MIN(D) from table) 
AND 
(select * from table where D = (select MAX(D) from table))

null means false else true

Upvotes: -1

David Marx
David Marx

Reputation: 8558

This technique is called a "self join."

SELECT a.*
FROM tableName a, tableName b
WHERE a.column_a = b.column_a
AND a.column_c < b.column_c
AND a.column_d > b.column_d

This assumes that the values in column_a are filled down and there aren't actually nulls as you showed in your example.

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Using ranking functions, it can be done something like this:

SELECT * FROM 
(
    SELECT 
        *
        , ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnC) AS RankC
        , ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnD) AS RankD
    FROM MyTable
) x
WHERE RankC <> RankD

SQLFiddle DEMO

Upvotes: 0

Related Questions