How to select row with max value when duplicate rows exist in SQL Server
You're basically just missing a status comparison since you want one row per status;
SELECT *
FROM WF_Approval sr1
WHERE NOT EXISTS (
SELECT *
FROM WF_Approval sr2
WHERE sr1.DocumentID = sr2.DocumentID AND
sr1.Status = sr2.Status AND # <-- new line
sr1.StepNumber < sr2.StepNumber
) AND MasterStepID = 'Approval1'
or rewritten as a JOIN
;
SELECT *
FROM WF_Approval sr1
LEFT JOIN WF_Approval sr2
ON sr1.DocumentID = sr2.DocumentID
AND sr1.Status = sr2.Status
AND sr1.StepNumber < sr2.StepNumber
WHERE sr2.DocumentID IS NULL
AND sr1.MasterStepID = 'Approval1';
SQLfiddle with both versions of the query here.
SQL select only rows with max value on a column
At first glance...
All you need is a GROUP BY
clause with the MAX
aggregate function:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
It's never that simple, is it?
I just noticed you need the content
column as well.
This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.
It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.
Basically, you have two approaches to solve that problem:
Joining with simple group-identifier, max-value-in-group
Sub-query
In this approach, you first find the group-identifier, max-value-in-group
(already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier
and max-value-in-group
:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
Left Joining with self, tweaking join conditions and filters
In this approach, you left join the table with itself. Equality goes in the group-identifier
. Then, 2 smart moves:
- The second join condition is having left side value less than right value
- When you do step 1, the row(s) that actually have the max value will have
NULL
in the right side (it's aLEFT JOIN
, remember?). Then, we filter the joined result, showing only the rows where the right side isNULL
.
So you end up with:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
Conclusion
Both approaches bring the exact same result.
If you have two rows with max-value-in-group
for group-identifier
, both rows will be in the result in both approaches.
Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".
Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.
SQL Select highest value where duplicate ID
select top (1)
with ties ID, DayNumber, Mfm, value
from
table
order by row_number() over (partiton by
ID, DayNumber, Mfm
order by value desc)
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten
table on BOTH fields:
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
how to get a Row with Max value of a column?
You can use a correlated subquery:
select t.*
from mytable t
where t.srno = (select max(srno) from mytable t1 where t1.p_id = t.p_id)
With an index on (p_id, srno)
, this should be an efficient solution.
Anoter common solution is to use row_number()
:
select pid, name, srno, rate
from (
select t.*, row_number() over(partition by p_id order by srno desc) rn
from mytable t
) t
where rn = 1
SQL Select Max(Date) out of rows with Duplicate Id
Try this:-
SELECT d.Id, d.UserId,d.CreatedDate,d.Status FROM
(
SELECT UserId, Max(CreatedDate) as MaxDate
FROM TableA
WHERE Status = 0
GROUP BY UserId
) r
INNER JOIN TableA d
ON d.UserId=r.UserId AND d.CreatedDate=r.MaxDate AND Status=0
Your GROUP BY
and JOIN
needed to be on UserId
for this to work, and I've moved the filter by Status=0
to the derived table / subquery.
Related Topics
Sql Server Table Locks in Long Query - Solution: Nolock
How to Add Months to a Current_Timestamp in Sql
How to Insert N Rows of Default Values into a Table
Query to Get Content Older Than 3 Weeks
Sql Order by a Column from Another Table
Handling Null in Greatest Function in Oracle
How to Display All The Tables with More Information (Create Date, Size,...) in a MySQL Database
How to Trim All Data in a Select * from Statement
How to Self-Join Table in a Way That Every Record Is Joined with The "Previous" Record
Postgresql - Conditional Ordering
Generate Create Scripts for a List of Indexes
How to Fire a Trigger Before a Delete in T-Sql 2005
Sql "If Exists..." Dynamic Query
Why Is There a Scan on My Clustered Index
How to Update an Xml Attribute Value in an Xml Variable Using T-Sql
How to Use Wildcards in "In" MySQL Statement
How to Insert into a Table with Just One Identity Column (Sql Express)