June 13, 2011 at 9:42 pm
I’m trying to get the next biggest date for a record with the same id.Below query gives me the right result but I need a code which is better than this.can you please help.My Manager is very picky about the way we write the code
select MIN(a.submit_date) as min_admit_date, b.submit_date, b.id
from orders b, orders a
where b.submit_date > a.submit_date and b.id = a.id and a.id ='932418'
group by b.submit_date,b.id
Result set
2008-05-12 00:00:00.0002008-04-31 00:00:00.000932418
2008-09-08 00:00:00.0002008-05-12 00:00:00.000932418
any help would be appreciated.
Thank you
June 13, 2011 at 11:50 pm
Would help if you provided the DDL for the table (CREATE TABLE statement), sample data (series of INSERT INTO statements), expected results based on the sample data. You have already provided us with your current code.
If you provide the requested information you get better answers plus tested code in return.
June 14, 2011 at 4:42 am
Instead of using group by, try using TOP 1 and an ORDER BY statement. That usually works better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2011 at 2:32 am
I'm not sure whether this will help you.. But I'm giving a try,
select id, max(submit_date)
from orders
where id = "xxx"
groupby id
June 16, 2011 at 4:49 am
Not sure what you’re trying to achieve however, would it not be actually be better to just list all the orders in descending order then your boss would see all the order dates.
***The first step is always the hardest *******
June 16, 2011 at 4:56 am
With the table structure and values as below:
create table a
(ID int, Orderdate datetime)
insert into a values
(1,GETDATE()),
(1,GETDATE()-3),
(1,GETDATE()-2),
(1,GETDATE()-1),
(1,GETDATE()-4),
(2,GETDATE()-1),
(2,GETDATE()-2),
(2,GETDATE()-3),
(2,GETDATE()-4)
The below query gives you the second highest date without having to perform joins.
with NextLargestDate (DateNumber,ID,OrderDate)
as
(
select row_number() over(partition by id order by orderdate desc) as DateNumber, ID,Orderdate
from a)
select ID,OrderDate
from NextLargestDate
where DateNumber = 2
Hope this helps you for having a better query concern.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply