I want to exclude records where id > 5 then select the top 1 of it order by date. How can I achieve this? Each record has audit_line which is unique field for each record. Recent SQL script is on below:
SELECT *
FROM db.table
HAVING COUNT(id) > 5
Asked
Active
Viewed 501 times
0
Dale K
- 25,246
- 15
- 42
- 71
-
`exclude records where id > 5 then select the top 1 of it` is a contradiction: you want to exclude or include these rows? – Charlieface Apr 14 '21 at 02:34
-
Hi @Charlieface If those records where id > 5. I want to include the top 1 of those 5 records order by date. So it will exclude the 4 records. – Arvin John Salandanan Apr 14 '21 at 04:06
-
possible duplicate https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group – Jayvee Apr 14 '21 at 08:19
-
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Jayvee Apr 14 '21 at 08:19
2 Answers
0
If you want id > 5 then you want where:
select top (1) t.*
from db.table t
where id > 5
order by date;
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
-
Hi This query will only fetch id > 5. Need a query to fetch where count(id) > 5. Then exclude the 4 records then get the top 1 record on it. – Arvin John Salandanan Apr 14 '21 at 04:14
0
You can use row-numbering for this.
Note that if you have no other column to order by, you can do ORDER BY (SELECT NULL), but then you may get different results on each run.
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY some_other_column) rn
FROM table
) t
WHERE rn = 5;
Charlieface
- 52,284
- 6
- 19
- 43