How do I write the following Query in SQL Server

Question

3.00/5 (2 votes)

See more:

SQL-Server-2008R2

, +

Here is the Scenario
I have table like this
OppID ModifyDate Old_Stage Value New_Stage Value

Opp_1 01-01-2013 Null Hold
Opp_1 05-11-2014 Hold Lost

I want to extract the latest Modify Date wrt Opportunity.
Note: I Need to Join this table with Others.
How can i write the query. Please Help !!

Posted 19-Nov-14 23:55pm

lovejeet0707

Add a Solution

4 solutions

Solution 2

Your question is a bit short on information.
But I believe something like this may work:

SQL

select 
    opp.*,
    oppi.*
from Opportunity opp
inner join (
          select OppID, max(ModifyDate) ModifyDateMax
          from Opportunity
          group by OppID
    ) oppMaxDt
    on 
          opp.OppID = oppMaxDt.OppID
          and 
          opp.ModifyDate = oppMaxDt.ModifyDateMax 
--additional table join(s) can go here
inner join Opp_Info oppi
	on opp.OppID = oppi.[Opp ID]
;

note: OppID and ModifyDate need to be unique for the query to return one record for each OppID and max ModifyDate

Posted 20-Nov-14 0:20am

jaket-cp

Updated 20-Nov-14 1:20am

v3

Comments

lovejeet0707 20-Nov-14 6:34am

HiActually problem is this only, I have Same Opportunity ID and different rows.
I just need the most recently Updated row.

jaket-cp 20-Nov-14 6:43am

Not sure what you are saying.
Are you saying the value for OppID never changes?

lovejeet0707 20-Nov-14 6:44am

Actually Query is Working out for me.....!
Thanks

lovejeet0707 20-Nov-14 6:51am

How can I write the additional join table,
for example Table Account - AccountName,AccountID
Contact- ContactName, ContactID.
Thanks in Advance :)

jaket-cp 20-Nov-14 6:57am

If the opportunity table has a AccountID or ContactID, then you can join it with that.
Have a read of http://www.w3schools.com/sql/sql_join.asp

lovejeet0707 20-Nov-14 6:58am

let it be table Opp_Info having Opportunity ID
than How can I join it...?

jaket-cp 20-Nov-14 7:04am

I believe you should ask another question about joins with the relevant table information.
But before doing that I suggest you have a little read about how "sql join" works.

lovejeet0707 20-Nov-14 7:10am

Sure but can you please for Once re-write your query showing joining of table
Table Name: Opp_Info
Column Name: Opp ID, Description,Status,Values
Please this help will be highly appreciated.

jaket-cp 20-Nov-14 7:14am

yes, no problem...
but if I do it, you need to update your question with this addition to the question :)
Also please read up on sql join :) :)

lovejeet0707 20-Nov-14 7:24am

Yeah for sure I will do it....!
I jst need to get Idea how u wrote the Query of joins !!

jaket-cp 20-Nov-14 7:29am

Solution edited with extra join to Opp_Info - hope that helps out.

Solution 3

SQL

Select OppID, ModifyDate ,Old_Stage_Value, New_Stage_Value from tablename
where ModifyDate in(Select max(ModifyDate) from tablename group by OppID)

it will display latest OppID data
check it will help

Posted 20-Nov-14 2:24am

Abid Shk

Updated 20-Nov-14 3:25am

v2

Solution 4

Hi
friend You also try this solution

SQL

DECLARE @Temp as table(fbalance int,fdate nvarchar(50),facno nvarchar(50))
insert into @Temp VALUES(100000.00,'Opp_2 01-01-2013','010001801000009')
insert into @Temp VALUES(80000.00,'Opp_1 05-11-2014','010001801000010')

SELECT * FROM @Temp
ORDER by SUBSTRING(fdate,len(fdate)-9,len(fdate)-(len(fdate)-10)) DESC

Posted 24-Nov-14 2:24am

lokesh lokesh

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-11-20T00:10:00

Solution 1

SQL

select * from Table order by ModifyDate desc

Will give you data by descending order of modification.

you can choose to have

SQL

select top 1 * from Table order by ModifyDate desc

to get latest modification date.

Other way is to apply Ranking function

SQL

select *,Row_Number() Over (order by ModifyDatedesc ) As LatestRecord from Table order by ModifyDate desc

and then you can filter LatestRecord column on 1 which will hold latest date.

You can also check this

SQL

SELECT * FROM TABLENAME ORDER BY PRIMARYCOLUMNNAME DESC
FETCH FIRST 1 ROWS ONLY ;

BUT THIS OPTION IS VALID ONLY IF THERE IS A PRIMARY KEY
COLUMN,OTHERWISE IT WONT WORK

Posted 20-Nov-14 0:10am

Shweta N Mishra

Updated 20-Nov-14 3:26am

v5

Comments

lovejeet0707 20-Nov-14 6:26am

I have Hundred Thousands Record like this in my Table.Its not feasible for me to first sort the data and than select top 1.
So I dont think first approach works.

and I am still doubtful what the second query is doing....!

Jörgen Andersson 20-Nov-14 7:14am

To get the latest ModifyDate you NEED to sort the data. If you find that this is not feasible it's only because you lack a proper index.
Add an index on (OppID,ModifyDate) and query with select TOP 1 * from Table where OppID = @OppID order by ModifyDate desc

Herman<T>.Instance 24-Nov-14 9:21am

I you create a descending Index om ModifyDate then top 1 is he first hit and always keeps on speed.

Shweta N Mishra 20-Nov-14 7:02am

select * from
(
select *,Row_Number() Over (order by ModifyDatedesc ) As LatestRecord from Table order by ModifyDate desc
) a where LatestRecord=1

You will get the latest record.

Jörgen Andersson 20-Nov-14 7:14am

Compensating faulty downvote

jaket-cp 20-Nov-14 7:23am

yes I agree

How do I write the following Query in SQL Server

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

How do I write the following Query in SQL Server

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us