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.
| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2011-02-18 : 16:39:59
|
| In Access, I used First function as below to make data from 1) to 2) as below query:SELECT ID, DATEFROM, First(duration) AS durationFROM TABLE1 GROUP BY MEMBID, DATEFROMHow to code in SQL to make result as 2)?1)ID--------DATEFROM--------DURATION 06000031--2009-07-22------1306000031--2009-07-22------2006000031--2010-07-22------406000051--2010-08-22------5006000051--2010-08-22------506000051--2010-08-22------802)ID--------DATEFROM--------DURATION 06000031--2009-07-22------1306000051--2010-08-22------50 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-18 : 17:03:57
|
| There's no FIRST() function in SQL Server. Row order does not matter in a true relational database. Unless you have another column you can order by that preserves the row order, you'll have to use MIN() or MAX(). |
 |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2011-02-18 : 17:21:23
|
| I knew there is no first function in SQL.MIN or MAX will make the different result. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-02-18 : 17:34:25
|
| So will FIRST(), if the table is ever reindexed. That's why physical storage order is meaningless, you can't rely on it to be consistent. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
First row in group function