Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Finding min using over(order by) in sql server Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:00 AM
Points: 16, Visits: 30
Hi All,

I am trying to write a sql to find the minimum of a dataset
i am using sql server.

select ST.BG_DTTM
,MIN(ST.BG_DTTM) over(ORDER BY ST.BG_DTTM) AS "Minim"
from
tablename ST

but it is giving me a error saying

Error: Incorrect syntax near 'order'. (State:37000, Native Code: 66)


any help is appreciated.

Thanks,
Sam.
Post #1422113
Posted Wednesday, February 20, 2013 9:06 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 672, Visits: 2,625
Hi

Is there any reason you cant use:

SELECT
MIN(ST.BG_DTTM) 'Minim'
FROM
tablename ST

Cheers

Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1422137
Posted Wednesday, February 20, 2013 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:00 AM
Points: 16, Visits: 30
if i go woth your suggestion then i need to group it by the other non aggregated columns, if i group it my results will go wrong, so i am thinking to use min(column1) over (order by column1) .

Any suggestion is appreciated.

Thanks,
Sam.
Post #1422170
Posted Wednesday, February 20, 2013 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:12 PM
Points: 214, Visits: 929
sunder.mekala (2/20/2013)
if i go woth your suggestion then i need to group it by the other non aggregated columns, if i group it my results will go wrong, so i am thinking to use min(column1) over (order by column1) .

Any suggestion is appreciated.

Thanks,
Sam.



Looks like you need to put your minimum part as a sub query if other columns are involved that you can't group by.
Post #1422173
Posted Wednesday, February 20, 2013 10:09 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 672, Visits: 2,625
if i go woth your suggestion then i need to group it by the other non aggregated columns, if i group it my results will go wrong, so i am thinking to use min(column1) over (order by column1) .


What other columns, there were non specified? Why will the results be wrong, in what way?

Sorry but without a little more information (i.e. DDL's and sample data would be perfect) we just can't see based upon this sorry..

Cheers

Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1422179
Posted Wednesday, February 20, 2013 10:18 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:02 AM
Points: 672, Visits: 2,625
Looks like you need to put your minimum part as a sub query if other columns are involved that you can't group by.


If this is what the OP is looking for then maybe..?:

(Just a guess )

SELECT 
<SomeCols>

FROM
Sometable AS ST

INNER JOIN -- If no NULLs are needed from this sub query
(
SELECT
<SomeID>
,MIN(ST.BG_DTTM) 'Minim'
FROM
tablename ST
GROUP BY
,<SomeID>

) AS SubQ

ON ST.<SomeID> = SubQ.<SomeID>



Cheers

Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1422192
Posted Wednesday, February 20, 2013 10:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
sunder.mekala (2/20/2013)
Hi All,

I am trying to write a sql to find the minimum of a dataset
i am using sql server.

select ST.BG_DTTM
,MIN(ST.BG_DTTM) over(ORDER BY ST.BG_DTTM) AS "Minim"
from
tablename ST

but it is giving me a error saying

Error: Incorrect syntax near 'order'. (State:37000, Native Code: 66)


any help is appreciated.

Thanks,
Sam.

I think you need PARTITION BY rather than ORDER BY when using an aggregate with the OVER() clause.

 
Post #1422194
Posted Wednesday, February 20, 2013 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:00 AM
Points: 16, Visits: 30
select PKG_NME, BG_DTTM, END_DTTM, PKG_STU
from
(select ST.PKG_NME, ST.BG_DTTM, ST.END_DTTM
,ST.PKG_STU
,BG_DTTM = select min(ST.BG_DTTM) from
,row_number() over(partition by ST.PKG_NME order by ST.BG_DTTM desc) as LatestRun
from
dbo.VW_JOB_EXCTN_RPT ST) ST1
where (ST1.LatestRun=1 and
PKG_NME like '%EDW_IDS%FACT')or
(ST1.LatestRun=1 and
PKG_NME like 'MST_EDW%CHILD_MASTER')
order by PKG_NME

Basically i have the above query which gives me PKG_NME which has latest time associated with it.

my requirement is among the PKG_NME from the above dataset which are assocated with BG_DTTM, END_DTTM, i have to find the difference of time between min(BG_DTTM) and max(END_DTTM) from the above dataset ONLY, here the reason why i am saying only is PKG_NME will have different BG_DTTM and END_DTTM as it is a logging table, but here i have find the difference between the LATEST MIN(BG_DTTM)) and max(END_DTTM).

basic table :

PKG_NME BG_DTTM END_DTTM
A 2/20/2013 11:19:50.000 2/20/2013 11:20:45.000
A 2/19/2013 10:20:20.000 2/19/2013 10:21:50.000
B 2/20/2013 11:20:30.000 2/20/2013 11:21:45.000
B 2/19/2013 11:19:50.000 2/19/2013 11:20:45.000
C 2/20/2013 21:38:50.000 2/20/2013 11:40:45.000
C 2/19/2013 11:35:50.000 2/19/2013 11:41:45.000


Above Query gives me

PKG_NME BG_DTTM END_DTTM
A 2/20/2013 11:19:50.000 2/20/2013 11:20:45.000
B 2/20/2013 11:20:30.000 2/20/2013 11:21:45.000
C 2/20/2013 21:38:50.000 2/20/2013 11:40:45.000


now i have to find out the difference min(BG_DTTM) that is 2/20/2013 11:19:50.000
and max(END_DTTM) that is 2/20/2013 11:40:45.000

i should get datediff(MM,min(BG_DTTM),max(END_DTTM)) = 40-19 = 21 Minutes in a seperate column.


Any help is appreciated.

Thanks,
Sam.
Post #1422199
Posted Wednesday, February 20, 2013 10:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:00 AM
Points: 16, Visits: 30
Sorry the above has some mistakes,pls consider this


select PKG_NME, BG_DTTM, END_DTTM, PKG_STU
from
(select ST.PKG_NME, ST.BG_DTTM, ST.END_DTTM
,ST.PKG_STU
,row_number() over(partition by ST.PKG_NME order by ST.BG_DTTM desc) as LatestRun
from
dbo.VW_JOB_EXCTN_RPT ST) ST1
where (ST1.LatestRun=1 and
PKG_NME like '%EDW_IDS%FACT')or
(ST1.LatestRun=1 and
PKG_NME like 'MST_EDW%CHILD_MASTER')
order by PKG_NME

Post #1422201
Posted Wednesday, February 20, 2013 1:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:55 AM
Points: 532, Visits: 984
Wrap the whole thing in another select and get your min and max values.
Post #1422276
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse