Query to get the minimum id

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 1, 2009 at 5:45 am

  #71524

  All,

  I have a table.

  create table log

  (

  ecode int,

  id int,

  datediff int

  )

  insert into log

  select 100,1,50

  union all

  select 100,2, 40

  union all

  select 100,3,80

  union all

  select 100,4,90

  union all

  select 100,5,70

  union all

  select 200,1,30

  union all

  select 200,2, 40

  union all

  select 200,3,80

  union all

  select 200,4,20

  union all

  select 200,5,70

  I need to display the id where the datediff has minimum value. I will pass Ecode as the input.

  i.e

  100

  id

  2

  200

  id

  4

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 1, 2009 at 5:48 am

  #1002663

  I used the below query to get the id.

  select id from

  log where ecode = 100

  and datediff = (select min(datediff)

  from log

  where ecode = 100)

  There is a small change in the requirement.

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 1, 2009 at 5:52 am

  #1002668

  The table contains only the date not datediff.

  so first i need to calculate the datediff,then i need to get the id.

  Modified Table Structure:

  Create table Log

  (

  ecode int,

  id int,

  Eff_Date datetime

  )

  insert into log

  select 100,1,'03/jun/2009'

  union all

  select 100,2,'02/jun/2009'

  union all

  select 100,3,'16/jun/2009'

  union all

  select 200,1,'07/jun/2009'

  union all

  select 200,2,'08/jun/2009'

  union all

  select 200,3,'05/jun/2009'

  Now, first i have to find out the datediff from the current date and then need to get the id where the datediff has minimum.

  karthik

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 1, 2009 at 5:57 am

  #1002669

  way to go on posting the table and data! it made it really easy to try and help!

  here's two examples; in one i figured you jsut wanted the ID, and the other assumes you want the whole record from "log":

  select min(Datediff) as theDatediff

  from [log]

  where ecode = 200

  --or if you need the whole record

  select log.* from log

  inner join (select ecode,min(datediff) as theDatediff from log group by ecode) MyAlias

  on log.ecode=MyAlias.ecode and log.Datediff = MyAlias.theDatediff

  where log.ecode=200

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 1, 2009 at 6:08 am

  #1002673

  ahh you changed it on me since i posted....

  i would switch tou using the row number function then...see how this works for you.

  run the inner query so you can see the results before it gets filtered by my RW alias for the row_number:

  select * from (

  select row_number() over (partition by ecode order by Datediff(minute,getdate(),Eff_Date) ) as RW,

  [log].*,

  Datediff(minute,getdate(),Eff_Date) as TheDateDiff

  from [log]) MyAlias

  where RW=1 and ecode=200

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  June 1, 2009 at 4:25 pm

  #1003088

  Lowell (6/1/2009)

  ---

  I would switch tou using the row number function then...

  I think he's using SQL 2000?

  I'm not sure if I'm over simplifying this, but is this what you are looking for Karthik?

  SELECT TOP 1

  *

  FROM

  (

  SELECT TOP 100 PERCENT

  ecode,

  ID,

  DIFF = DATEDIFF(MINUTE,GETDATE(),Eff_Date)

  FROM log

  ORDER BY id,DIFF ASC

  ) l

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 1, 2009 at 4:36 pm

  #1003094

  ahh you are right Greg, i did not notice that. Thanks for the better solution.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  June 1, 2009 at 6:31 pm

  #1003110

  Greg Snidow (6/1/2009)

  ---

  Lowell (6/1/2009)

  ---

  I would switch tou using the row number function then...

  I think he's using SQL 2000?

  I'm not sure if I'm over simplifying this, but is this what you are looking for Karthik?

  SELECT TOP 1

  *

  FROM

  (

  SELECT TOP 100 PERCENT

  ecode,

  ID,

  DIFF = DATEDIFF(MINUTE,GETDATE(),Eff_Date)

  FROM log

  ORDER BY id,DIFF ASC

  ) l

  Actually, I think your requirements would need the ORDER BY to be ORDER BY DIFF ASC, id. Sorry for the confusion.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 2, 2009 at 4:10 am

  #1003277

  Greg,

  it is not giving the expected result. I have to get the minimum Eff_date(where Eff_date is greater than getdate() ) then the correspoinding id should be displayed.

  karthik

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  June 2, 2009 at 5:18 am

  #1003308

  Doesn't a simple select with TOP & ORDER BY clause do the task?

  SELECTTOP 1 id, Eff_Date, DATEDIFF( MINUTE, Eff_Date, GETDATE() ) AS DateDifference

  FROM[log]

  WHEREecode = 100

  ORDER BY DateDifference

  --Ramesh
  

  ---

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 2, 2009 at 8:15 am

  #1003425

  No...

  Expected result:

  100,2,'02/jun/2009'

  200,3,'05/jun/2009'

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  June 2, 2009 at 8:17 am

  #1003426

  A small modification in the code

  SELECT TOP 1 id, Eff_Date, DATEDIFF( MINUTE, Eff_Date, GETDATE() ) AS DateDifference

  FROM [log]

  WHERE ecode = 200

  ORDER BY DateDifference desc

  will give the expected result.

  karthik

• Saurabh Dwivedy

  SSCommitted

  Points: 1887

  More actions

  June 2, 2009 at 10:15 am

  #1003512

  create table log

  (

  ecode int,

  id int,

  eff_date datetime

  )

  insert into log

  select 100,1,'03/jun/2009'

  union all

  select 100,2,'02/jun/2009'

  union all

  select 100,3,'16/jun/2009'

  union all

  select 200,1,'07/jun/2009'

  union all

  select 200,2,'08/jun/2009'

  union all

  select 200,3,'05/jun/2009'

  ;with test AS (

  select ecode, ID,

  eff_date,

  ROW_NUMBER() over(partition by ecode order by abs(datediff(day, eff_date, GETDATE())) asc) AS date_diff

  from log

  )

  select ecode, id, eff_date

  from test

  where date_diff = 1

  order by ecode, id

  Saurabh Dwivedy
  ___________________________________________________________

  My Blog: http://tinyurl.com/dwivedys

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537

  Be Happy!