SQL Equivalent of DMIN( ) in MS Access

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

Other Forums

MS Access

SQL Equivalent of DMIN( ) in MS Access

Author

Topic

genius_palli
Starting Member

42 Posts

Posted - 2008-12-01 : 02:06:39

Hi Everyone..
In MS Access, we have DMin function, do we have any equivalent function in SQL Server. Or is there any other way to do the same job.

Please help. Thanks in advance for your suggestions.

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 02:13:27

nope you need to write query for that like

SELECT Min(column) 
FROM table
WHERE condition

genius_palli
Starting Member

42 Posts

Posted - 2008-12-01 : 05:36:41

Thanks buddy... :-)

Every experience has something to learn. Go get it.
Thanks and Regards
genius_palli
Web Developer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 05:42:27

With SQL Server 2005 and later, you can use the windows functions to PARTITION BY same thing.

E 12°55'05.63"
N 56°04'39.26"

genius_palli
Starting Member

42 Posts

Posted - 2008-12-01 : 07:44:39

Hi Peso..
Could you please define bit in detail how to use the windows functions.
Frankly speaking , i had got the solution to my problem by writing subquery for DMin function as Visakh16 suggested.
But I just want to learn about windows functions.
Thanks lot for replying.

Every experience has something to learn. Go get it.
Thanks and Regards
genius_palli
Web Developer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 07:54:31

[code]DECLARE @Sports TABLE
(
Team VARCHAR(20),
Region VARCHAR(20),
Points INT
)

INSERT @Sports
SELECT 'AIK', 'Stockholm', 49 UNION ALL
SELECT 'Djurgården', 'Stockholm', 40 UNION ALL
SELECT 'Elfsborg', 'Västra', 50 UNION ALL
SELECT 'GAIS', 'Västra', 27 UNION ALL
SELECT 'Gefle', 'Norra', 31 UNION ALL
SELECT 'Göteborg', 'Västra', 36 UNION ALL
SELECT 'Häcken', 'Västra', 22 UNION ALL
SELECT 'Halmstad', 'Västra', 27 UNION ALL
SELECT 'Hammarby', 'Stockholm', 43 UNION ALL
SELECT 'Helsingborg', 'Södra', 42 UNION ALL
SELECT 'Kalmar', 'Östra', 41 UNION ALL
SELECT 'Malmö', 'Södra', 38 UNION ALL
SELECT 'Örgryte', 'Västra', 17 UNION ALL
SELECT 'Öster', 'Östra', 19

SELECT Team,
Points,
Region,
ROW_NUMBER() OVER (ORDER BY Points DESC) AS TotPl,
RANK() OVER (ORDER BY Points DESC) AS TotRank1,
DENSE_RANK() OVER (ORDER BY Points DESC) AS TotRank2,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Points DESC) AS RgnPl,
RANK() OVER (PARTITION BY Region ORDER BY Points DESC) AS RgnRank1,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY Points DESC) AS RgnRank2,
MAX(Points) OVER (PARTITION BY Region) AS RgnMax,
NTILE(3) OVER (ORDER BY Points DESC) AS Layer
FROM @Sports
ORDER BY Points DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

genius_palli
Starting Member

42 Posts

Posted - 2008-12-02 : 03:35:22

It seems to be looking great.. I have used table variables but not these Windows functions.
Thanks Peso for your reply, i will try to use them.

Every experience has something to learn. Go get it.
Thanks and Regards
genius_palli
Web Developer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 04:14:25

The use of table variable is only to get sample data because I have no access to your data.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources