CODEWITHSUNDEEP
sqlsql-server
GertDeWilde
GertDeWilde

Reputation: 351

SQL Select TOP 1 for each group in subquery

Good morning,

I want to alter my query in such a way, that only the top 1, filtered from h.started asc is selected.

select h.started, *  from wshhistory h
join asset a on h.assetid = a.uid
inner join 
(
    select Count(*) as TotalLatest, a.uid, a.deleted from asset a
    join wshhistory h on a.uid = h.assetid
    where h.latest = 1 
    group by a.uid, a.deleted
    having Count(*) > 1
) X
on X.uid = h.assetid
where X.deleted = 0 and h.latest = 1
order by h.assetid desc

I searched all over, and found in most posts, to use: 

ROW_NUMBER() OVER (PARTITION BY a.uid ORDER BY h.started asc) as rn

But I can't seem to use this since I need use group by, and this results in the error message:

Column 'wshhistory.started' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

To give some extra info about my query: I need to search where I have duplicates of Latest = 1 (table: wshhistory), of the same assetid. And then I need to set the them all on 0 except the latest one.

Upvotes: 0

Views: 1947

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

I think you want something like this:

with toupdate as (
      select h.*,
             row_number() over (partition by h.assetid order by h.started desc) as seqnum
      from wshhistory h
      where h.latest = 1
     )
update toupdate
    set latest = 0
    where seqnum > 1 and
          exists (select 1
                  from asset a
                  where a.uid = toupdate.assetid and a.deleted = 0
                 );

Sample data and desired results are much easier to work with than non-working queries.

Upvotes: 1

Related Questions