Distinct without group by/having/distinct [duplicate]

Question

I am trying to find an appropriate solution to the following problem: I have an activity-tracking database and I want to get the status at a specific date (ex: 11-Sept)

Activity	Status	Date
100	Done	12-Sept
100	In prog	10-Sept
110	In prog	12-Sept
110	In prog	09-Sept
110	New	08-Sept

My current query is: select * from table where Date <= 11-Sept My current output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept
110	New	08-Sept

Problem is I want to limit to only 1 row (newest date) for each activity code. Desired output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept

BUT...

I can't user group by, having or distinct, so the following solutions are not acceptable:

select distinct Name
from table
where Date <= 11-Sept

select name, max(date)
from table 
where Date <= 11-Sept
group by name

Basically.. I can only use the where clause

Anyone has an idea?

Desired output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept

To be mentioned: the format of the date here is simplified

11-Sept is a string, not a date and can't be sorted in date order. What does Date actually contain? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 12, 2023 at 10:05
I assume your date format is just your shorthand because if that's what you data looks like you are in trouble. — Dale K
– Dale K, Commented Sep 12, 2023 at 10:05
If you can only use the WHERE clause you need to show us the exact query that this will slot into. Not just some approximation to it. As it will need to be a sub query correlated to the outer query that you have not shown us — Martin Smith
– Martin Smith, Commented Sep 12, 2023 at 10:18

Tim Biegeleisen · Accepted Answer · 2023-09-12 10:13:33Z

6

One canonical way to do this uses the ROW_NUMBER() window function:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Activity ORDER BY Date DESC) rn
    FROM yourTable
    WHERE Date < '20230911'
)

SELECT Activity, Status, Date
FROM cte
WHERE rn = 1
ORDER BY Activity;

Another more concise (yet less performant) way:

SELECT TOP 1 WITH TIES Activity, Status, Date
FROM yourTable
WHERE Date <= '20230911'
ORDER BY ROW_NUMBER() OVER (PARTITION BY Activity ORDER BY Date DESC);

A third option, using exists logic:

SELECT Activity, Status, Date
FROM yourTable t1
WHERE
    Date <= '20230911' AND
    NOT EXISTS (
        SELECT 1
        FROM yourTable t2
        WHERE t2.Date <= '20230911' AND
              t2.Activity = t1.Activity AND
              t2.Date > t1.Date
    )
ORDER BY Activity;

edited Sep 12, 2023 at 10:13

answered Sep 12, 2023 at 10:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

userav Over a year ago

I cannot use cte and I cannot use top 1. I need a different solution. I can edit only the where clause of the SQL statement

Dale K Over a year ago

Please explain where these limitations come from

userav Over a year ago

The query passes through an application, that allows me to edit only the where-clause.

Dale K Over a year ago

You need to put that sort of information in your question from the start

siggemannen Over a year ago

@DaleK no, it's much more fun to create new roadblocks so people can waste time on solutions that don't work

|

siggemannen · Accepted Answer · 2023-09-12 10:14:08Z

2

You can do something like this:

select *
into #data
from 
(
    VALUES  (100, N'Done', N'20230912')
    ,   (100, N'In prog', N'20230910')
    ,   (110, N'In prog', N'20230912')
    ,   (110, N'In prog', N'20230909')
    ,   (110, N'New', N'20230908')
) t (Activity,Status,Date)

select *
from #data d
where date <= '20230911'
and not exists(
    select 1
    from  #data d2
    where d2.Activity = d.activity
    and d2.date > d.date
    and d2.date <= '20230911'
    )

This allows to get the top date where there are no dates above it

answered Sep 12, 2023 at 10:14

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Comments

PeterClemmensen · Accepted Answer · 2023-09-12 10:22:03Z

-2

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
Activity INT         NOT NULL,
Status   VARCHAR(20) NOT NULL,
Date     DATE        NOT NULL
)
;

INSERT INTO #t
VALUES
(100, 'Done'   , '2023-09-12'),
(100, 'In prog', '2023-09-10'),
(110, 'In prog', '2023-09-12'),
(110, 'In prog', '2023-09-09'),
(110, 'New'    , '2023-09-08')
;

SELECT *
FROM #t AS t
WHERE DATE <= '2023-09-11'
AND NOT EXISTS(
SELECT *
FROM  #t AS t2
WHERE t2.Activity = t.activity
AND t2.date > t.date
AND t2.date <= '2023-09-11'
)

answered Sep 12, 2023 at 10:22

PeterClemmensen

4,9673 gold badges18 silver badges27 bronze badges

2 Comments

Dale K Over a year ago

That's identical to existing answers

Jonas Metzler Over a year ago

Wow, the fourth answer with the same query ;)

Collectives™ on Stack Overflow

Distinct without group by/having/distinct [duplicate]

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Linked

Related