CODEWITHSUNDEEP
sqlsql-serverdateselect
rj487
rj487

Reputation: 4634

SQL Server : select the minimum value from table

I know it's simple question, but I still can't figure it out.

I want to find the date which is the closest date from now.

Here is my product table:

P_INDATE
----------    
2013-11-03  
2013-12-13 
2013-11-13

Basically, it should show 2013-12-13.

I type this SELECT Max( P_INDATE) FROM product and it work.

Then, I try to use MIN((GETDATE()- P_INDATE)) in the where condition, but I fail.

Upvotes: 0

Views: 94

Answers (5)

Ionic
Ionic

Reputation: 3935

Well you can try this:

SELECT TOP(1) P_INDATE
FROM [product table]
ORDER BY CASE 
            WHEN DATEDIFF(day,P_INDATE,GETDATE()) < 0 
            THEN DATEDIFF(day,GETDATE(),P_INDATE) 
            ELSE DATEDIFF(day,P_INDATE,GETDATE()) 
        END ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If you have an index on the column, the most efficient method is probably a bit more complicated:

SELECT TOP 1 P_INDATE
FROM ((SELECT TOP 1 P_INDATE
       FROM product 
       WHERE P_INDATE < GETDATE()
       ORDER BY P_INDATE DESC
      ) UNION ALL
      (SELECT TOP 1 P_INDATE
       FROM product
       WHERE P.INDATE >= GETDATE()
       ORDER BY P.INDATE
      )
     )
ORDER BY ABS(DATEDIFF(second, P_INDATE, GETDATE()))

The subqueries will use the index to get (at most) one row earlier and later than the current date. The outer ORDER BY then just needs to sort two rows.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312319

One way to go about this is to order the query by the difference between the stored date and the current date and take the first rows only. Using abs will allow you to find the closest date regardless of whether its before or after the current date.

SELECT   TOP 1 p_indate
FROM     mytable
ORDER BY ABS(GETDATE() - p_indate) ASC

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28938

Assuming you have a column which stores data and you want to show only recent one every time,why cant you use

select max(date) from yourtable which will always give you recent date

Upvotes: 0

krivtom
krivtom

Reputation: 24916

Use MAX and WHERE clause along with function GETDATE():

SELECT MAX(P_INDATE)
FROM product 
WHERE P_INDATE < GETDATE()

The above query gives you maximum date, which is less than current date, which you get using function GETDATE()

Upvotes: 1

Related Questions