select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 09:48:40

from the table,
value date
100 21/10/2011
139 22/10/2011
140 23/10/2011

I want the value for the minimum date, that is
output should be
100

Could anyone please write a query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 09:54:16

[code]
SELECT TOP 1 value
FROM table
ORDER BY date ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-15 : 09:57:43

If you have mutiple record against single (minimum) date then

SELECT * FROM table
WHERE [date] IN (SELECT MIN([date]) FROM table)

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 09:59:44

even this would do


SELECT TOP 1 WITH TIES
value
FROM table
ORDER BY date ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-15 : 10:05:50

Visakh, You are brillient.

--------------------------
http://connectsql.com/

gagani
Posting Yak Master

112 Posts

Posted - 2011-11-15 : 10:17:50

sorry, i didnt put it right
id value status datecolumn
101 100 1 21/01/2011
101 110 2 22/01/2011
101 120 1 23/01/2011
102 300 1 25/01/2011
102 400 2 26/01/2011
for the given table, i need the value when status = 1 and within that status i want to choose the value for minimum date
output should be
101 100
102 300

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 10:21:13

[code]
SELECT t.*
FROM table t
INNER JOIN (SELECT id,min(datecolumn) as datevalue
WHERE status = 1
GROUP BY id
)t1
ON t1.id = t.id
AND t1.datevalue = t.datecolumn
WHERE t.status =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-16 : 05:02:11

quote:
Originally posted by visakh16

SELECT t.*
FROM table t
INNER JOIN (SELECT id,min(datecolumn) as datevalue from table
WHERE status = 1
GROUP BY id
)t1
ON t1.id = t.id
AND t1.datevalue = t.datecolumn
WHERE t.status =1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources